USE [ReportServerTempDB]GOCREATE PROCEDURE [dbo].[SeachTablePage](@TableName VARCHAR(200),– 表名@Fileds VARCHAR(500),– 查询的字段@OrderFiled VARCHAR(100),– 排序字段@IsDesc BIT ,– 是否降序排序@WhereString VARCHAR(2000), — 查询字段@PageIndex INT ,– 当前页数@PageSize INT ,– 每页条数@TotalRecord INT OUTPUT– 返回总条数)ASBEGINDECLARE @OrderString VARCHAR(500)IF(@PageIndex IS NULL OR @PageIndex <= 0)BEGINSET @PageIndex = 1ENDIF(@PageSize IS NULL OR @PageSize <= 0)BEGINSET @PageSize = 10ENDDECLARE @StartRowID INTDECLARE @EndRowID INTSET @StartRowID = (@PageIndex – 1) * @PageSize + 1SET @EndRowID = @PageIndex * @PageSizeIF (@WhereString is null OR @WhereString = '')BEGINSET @WhereString = '1 = 1'ENDIF (@OrderFiled IS NULL OR @OrderFiled = '')BEGINSET @OrderFiled = 'CreateDate'ENDIF (@IsDesc IS NULL OR @IsDesc = 1)BEGINSET @OrderString = @OrderFiled + ' DESC'ENDELSEBEGINSET @OrderString = @OrderFiled + ' ASC'ENDDECLARE @TotalSQL NVARCHAR(2000)SET @TotalSQL = 'SELECT @Total = COUNT(*) FROM ' + @TableName + ' WHERE ' + @WhereString + ''EXEC sp_executesql @TotalSQL , N'@Total BIGINT OUT' , @TotalRecord OUTPUT — 返回总记录数DECLARE @SelectSQL NVARCHAR(3000)IF(@TotalRecord <= @PageSize AND @PageIndex = 1)BEGINSET @SelectSQL = 'SELECT ' + @Fileds + ' FROM ' + @TableName + ' WHERE ' + @WhereString + ' ORDER BY ' + @OrderStringENDELSEBEGINSET @SelectSQL = 'SELECT row_number() OVER (ORDER BY ' + @OrderString + ') AS RowId,' + @Fileds + ' FROM ' + @TableName + ' WHERE ' + @WhereStringSET @SelectSQL = 'SELECT * FROM (' + @SelectSQL + ') AS tab WHERE RowId BETWEEN ' + ltrim(STR(@StartRowID)) + ' AND ' + ltrim(STR(@EndRowID)) + ''ENDprint @SelectSQLEXEC (@SelectSQL)END
，获得幸福的二法门是珍惜你所拥有的、遗忘你所没有的
