四种查询SQL语句方法及两种存储过程_sql查询有哪些存储过程

//四种查询的方法：
 
第一种（top）：select top 10 * from dbo.Admin where Id not in(select top 10 Id  from dbo.Admin)
 
第二种（max）：select top 10 * from dbo.Admin where Id >
 
              (select Max(Id) from dbo.Admin where Id in(select top 10 Id from dbo.Admin))
 
第三种（between-and）：select * from dbo.Admin where Id between 11 and 20;
 
第四种（row-number）：select * from (select *, Row_Number()over(order by Id)as number from dbo.Admin)t 
 
                      where t.number between 11 and 20;
 

//循环存储过程：
 
CREATE PROCEDURE XH(@Z int)
as 
declare @a int;
set @a = @Z;
while(@a>0)
begin
print @a;
 set @a = @a - 1;
END
 
//分页存储过程：
 
create procedure sqlvj
(
    @YS int,
    @XSTS int,
    @BM varchar(200),
    @ZJ varchar(100),
    @LM varchar(50),
    @PX varchar(100)
) 
as
DECLARE @sql varchar(200);
DECLARE @A1 varchar(50);
DECLARE @A2 Varchar(50);
 
SET @A1 = CAST((@YS-1)*@XSTS as varchar(50));
SET @A2 = CAST(@YS*@XSTS as varchar(50));
SET @sql = CAST('select ' +@LM+ ' from ' +@BM+ ' where ' + @ZJ+ ' BETWEEN ' +@A1+ ' AND ' +@A2+ ' ORDER BY ' +@ZJ+ ' ' +@PX+ ' ' as varchar(200));
exec(@sql)
 
//调循环和分页的方法：
 
exec XH 5;
 
exec sqlvj 1,10,'Admin','Id','*','ASC';