多条件分页存储过程

最新推荐文章于 2019-05-30 17:08:22 发布

renyanbinnet

最新推荐文章于 2019-05-30 17:08:22 发布

阅读量586

点赞数

文章标签：存储 go

本文链接：https://blog.youkuaiyun.com/renyanbinnet/article/details/1558997

版权

CREATE PROCEDURE    GetPageRecordsByMoreOrder
(
@tblname   nVARCHAR(4000),    --表名
@PageIndex int,              --页码
@PageSize int,       --每页容纳的记录数
@IsCount     bit,
@ID      VARCHAR(255),       --需要排序的不重复的ID号
@strWhere   VARCHAR(255),      --查询条件及规则
@OrderType   VARCHAR(255)     --排序字段及规则
)
AS

DECLARE @Str nVARCHAR(4000)

if @IsCount !=1
begin
               if @strWhere =''
                     begin
                             SET @Str='SELECT   TOP '+CAST(@PageSize AS VARCHAR(20))+' * FROM '+@tblname+'   WHERE '+@ID+'   NOT IN
                   (SELECT   TOP '+CAST((@PageSize*(@PageIndex-1)) AS VARCHAR(20))+' '+@ID+' FROM '+@tblname+' ORDER BY '+@OrderType+') ORDER BY '+@OrderType
                     end
                else
                      begin
                             SET @Str='SELECT   TOP '+CAST(@PageSize AS VARCHAR(20))+' * FROM   '+@tblname+' WHERE '+@ID+' NOT IN
                  (SELECT   TOP '+CAST((@PageSize*(@PageIndex-1)) AS VARCHAR(20))+' '+@ID+' FROM '+@tblname+' where '+@strWhere+'    ORDER BY '+@OrderType+') and   '+@strWhere+'   ORDER BY '+@OrderType
                      end
end
else
begin
              if @strWhere =''
                     begin
                           SET @Str='select count(*) as Total from   ' + @tblname
                     end
                else
                      begin
                            SET @Str='select count(*) as Total from   ' + @tblname + ' where('+@strWhere+')'
                      end
end
print @str
EXEC sp_ExecuteSql @Str
GO