三种SQL分页查询的存储过程

--根据MAX(MIN)ID CREATE PROC [dbo].[proc_select_id] @pageindex int=1,--当前页数 @pagesize int=10,--每页大小 @tablename VARCHAR(50)='',--表名 @fields VARCHAR(1000)='',--查询的字段集合 @keyid VARCHAR(50)='',--主键 @condition NVARCHAR(1000)='',--查询条件 @orderstr VARCHAR(500),--排序条件 @totalRecord BIGINT OUTPUT--总记录数 AS     IF ISNULL(@orderstr,N'')=N'' SET @orderstr=N' ORDER BY '+@keyid+N' DESC '     IF ISNULL(@fields,N'')=N'' SET @fields=N'*'     IF ISNULL(@condition,N'')=N'' SET @condition=N'1=1'     DECLARE @sql NVARCHAR(4000)     --IF(@totalRecord IS NULL)     --BEGIN         SET @sql=N'SELECT @totalRecord=COUNT(*)'             +N' FROM '+@tablename             +N' WHERE '+@condition         EXEC sp_executesql @sql,N'@totalRecord INT OUTPUT',@totalRecord OUTPUT     --END     IF(@pageindex=1)     BEGIN         SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr         EXEC(@sql)     END     ELSE     BEGIN         DECLARE @operatestr CHAR(3),@comparestr CHAR(1)         SET @operatestr='MAX'         SET @comparestr='>'         IF(@orderstr<>'')         BEGIN             IF(CHARINDEX('desc',LOWER(@orderstr))<>0)             BEGIN                 SET @operatestr='MIN'                 SET @comparestr='<'             END         END         SET @sql=N'SELECT top '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@keyid+@comparestr             +N'(SELECT '+@operatestr+N'('+@keyid+N') FROM '+@tablename+N' WHERE '+@keyid             +N' IN (SELECT TOP '+STR((@pageindex-1)*@pagesize)+N' '+@keyid+N' FROM '+@tablename+N' WHERE '             +@condition+N' '+@orderstr+N')) AND '+@condition+N' '+@orderstr         EXEC(@sql)     END GO       --根据ROW_NUMBER() OVER CREATE PROC [dbo].[proc_select_page_row] @pageindex INT=1,--当前页数 @pagesize INT=10,--每页大小 @tablename VARCHAR(50)='',--表名 @fields VARCHAR(1000)='*',--查询的字段集合 @keyid VARCHAR(50)='',--主键 @condition NVARCHAR(1000)='',--查询条件 @orderstr VARCHAR(500),--排序条件 @totalRecord BIGINT  OUTPUT--总记录数 AS     IF ISNULL(@orderstr,N'')=N'' SET @orderstr=N' ORDER BY '+@keyid+N' DESC '     IF ISNULL(@fields,N'')=N'' SET @fields=N'*'     IF ISNULL(@condition,N'')=N'' SET @condition=N'1=1'     DECLARE @sql NVARCHAR(4000) --  IF @totalRecord IS NULL --  BEGIN         SET @sql=N'SELECT @totalRecord=COUNT(*)'             +N' FROM '+@tablename             +N' WHERE '+@condition         EXEC sp_executesql @sql,N'@totalRecord bigint OUTPUT',@totalRecord OUTPUT --END     IF(@pageindex=1)     BEGIN         SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr         EXEC(@sql)     END     ELSE     BEGIN         DECLARE @StartRecord INT         SET @StartRecord = (@pageindex-1)*@pagesize + 1         SET @sql=N'SELECT * FROM (SELECT ROW_NUMBER() OVER ('+ @orderstr +N') AS rowId,'+@fields+N' FROM '+ @tablename+N') AS T WHERE rowId>='+STR(@StartRecord)+N' and rowId<='+STR(@StartRecord + @pagesize - 1)         EXEC(@sql)     END GO       --根据TOP ID CREATE PROC [dbo].[proc_select_page_top] @pageindex INT=1,--当前页数 @pagesize INT=10,--每页大小 @tablename VARCHAR(50)='',--表名 @fields VARCHAR(1000)='',--查询的字段集合 @keyid VARCHAR(50)='',--主键 @condition NVARCHAR(1000)='',--查询条件 @orderstr VARCHAR(500),--排序条件 @totalRecord BIGINT OUTPUT--总记录数 AS     IF ISNULL(@orderstr,N'')=N'' SET @orderstr=N' ORDER BY '+@keyid+N' DESC '     IF ISNULL(@fields,N'')=N'' SET @fields=N'*'     IF ISNULL(@condition,N'')=N'' SET @condition=N'1=1'     DECLARE @sql NVARCHAR(4000)     --IF(@totalRecord IS NULL)     --BEGIN         SET @sql=N'SELECT @totalRecord=COUNT(*)'             +N' FROM '+@tablename             +N' WHERE '+@condition         EXEC sp_executesql @sql,N'@totalRecord INT OUTPUT',@totalRecord OUTPUT     --END     IF(@pageindex=1)     BEGIN         SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE '+@condition+N' '+@orderstr         EXEC(@sql)     END     ELSE     BEGIN         SET @sql=N'SELECT TOP '+STR(@pagesize)+N' '+@fields+N' FROM '+@tablename+N' WHERE  '+@keyid         +N' NOT IN(SELECT TOP '+STR((@pageindex-1)*@pagesize)+N' '+@keyid+N' FROM '         +@tablename+N' WHERE '+@condition+N' '+@orderstr+N') AND '+@condition+N' '+@orderstr         EXEC(@sql)     END GO

 

转载于:https://www.cnblogs.com/richzhang/archive/2013/03/27/2984823.html