sql存储

1.查询前十条的四个SQL语句

--第一种
select top 10* from dbo.AdminInfo where AdminId not in(select top 10 AdminId from dbo.AdminInfo)
--第二种
select top 10* from dbo.AdminInfo where AdminId>(select MAX(AdminId) from dbo.AdminInfo where AdminId in(select top 10 AdminId from dbo.AdminInfo))
--第三种
select * from dbo.AdminInfo where AdminId between 11 and 20
--第四种
select * from (select *,ROW_NUMBER() over(Order by AdminId)as number from dbo.AdminInfo)t where t.number between 11 and 20;

2.页面存储

create proc pagclistproc (@pageIndex int,@pagesize int,@tableName varchar(200),@ColumnName varchar(500),@Orderby varchar(50),@Sort varchar(50)
)as
Declare @sql nvarchar(2000);
set @sql= 'select ' +@ColumnName +' from (select ' +@ColumnName + ' ,ROW_NUMBER() over(Order by ' +@Orderby+ ' )as number from ' +@tableName + ' )t where t.number between ' +CAST(((@pageIndex-1)*@pagesize)as varchar(200))+ ' and ' +CAST((@pageIndex*@pagesize)as varchar(200))
exec(@sql)
exec pagclistproc 1,10,'dbo.AdminInfo','*','AdminId','desc';

3.sql中创建循环的存储过程
 

create procedure xuanhuan (@count int )
as 
declare @num int ;    
set @num=@count;    
while(@num>0)       
begin                 
print @num;          
set @num-=1;       
end                   
exec dbo.xuanhuan 3