SQL_2012新版分页存储

SQL_2012新版分页存储

ALTER PROCEDURE [dbo].[PagingStoredProcedure]
    @tableName VARCHAR(MAX) ,          --表名
    @fields VARCHAR(2000) ,       --返回字段(多个以逗号分隔)
    @keyField VARCHAR(2000),        --唯一键,为空时采用ROW_NUMBER分页（废弃）
    @orderFields VARCHAR(2000) ,    --排序字段(多个以逗号分隔)例:a desc,b asc
    @where VARCHAR(MAX) ,           --查询条件 例1:where id=1 ;例2 and id=1
    @pageSize INT ,                 --页尺寸
    @pageIndex INT ,                --页码
    @total INT OUTPUT               --返回总记录数
AS 
    DECLARE @Sql VARCHAR(MAX)       --主语句
    DECLARE @OrderSql VARCHAR(200)  --排序
    DECLARE @TotalSql NVARCHAR(MAX) --查询记录总数主语句

	DECLARE @whereIndex  int 
	SET @whereIndex = CHARINDEX('where',@where,0)
    --条件sql
    IF  @whereIndex = 0  OR @whereIndex > 10
        SET @where = ' where 1=1 ' + @where --p.DOMAIN_ID = 1 AND p.VALID = 1
    ELSE
        SET @where=' '+@where
    --排序sql
    SET @OrderSql = ' order by ' + @orderFields --p.ID desc
 
    --------------总记录数---------------
	IF @pageindex=0
	SET @pageindex = 1

    SET @total = 0
    --查询总记录数进行赋值
	SET @TotalSql = 'select @TotalCout=count(*) from  ' + @tableName +' '+ @where     	
	EXEC sp_executesql @TotalSql, N'@TotalCout int output', @total OUTPUT

	SET @Sql = 'SELECT '+ @fields +' FROM '+ @tableName + @where
	DECLARE @sqlCount NVARCHAR(max)
	SET @sql = @sql + @OrderSql +' offset '+ CAST(@pagesize * (@pageindex-1) AS VARCHAR(10)) + ' rows fetch next '+CAST(@pagesize AS varchar)+' rows only '
	
	print(@sql)	
	EXEC(@sql)

旧版本分页存储

ALTER  procedure [dbo].[GetPage]
    @tblName      varchar(1000),       -- 表名(必要参数)
    @Fields      varchar(1000)='*',      --要返回的列，要保证没有名称的列有别名
    @PageSize     int = 25,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码
    @OrderType    varchar(50) = '',    -- 设置排序类型,不要加order by 
    @strWhere     varchar(2000) = ''  -- 查询条件 (注意: 不要加 where)
as 
BEGIN
    declare @Mycount varchar(2000)---此变量存储SQL语句，用于获得符合条件的记录总数
    declare @strSql varchar(2000)----此变量存储SQL语句，用于获得符合条件的记录
    if @OrderType=''
    set @OrderType='getdate()'

    if(@strWhere='')
    begin
        set @Mycount='select count(*) from '+@tblName
        set @strsql='select * from (select top ('+cast(@PageSize*@PageIndex as varchar(10))+') ROW_NUMBER()over(order by '+@OrderType+') rowNumber,'+@Fields+' from '+@tblName+')  t where rowNumber>'+cast((@PageIndex-1)*@PageSize as varchar(10))
    end
    else
    begin
        set @Mycount='select count(*) from '+@tblName+' where '+@strWhere
        set @strsql='select * from (select top ('+cast(@PageSize*@PageIndex as varchar(10))+') ROW_NUMBER()over(order by '+@OrderType+') rowNumber,'+@Fields+' from '+@tblName+' where '+@strWhere+')  t where rowNumber>'+cast((@PageIndex-1)*@PageSize as varchar(10))
    end  
        exec(@strSql)
        exec(@Mycount)
END