SQL Server 分頁預存程序，sql預存程序

SQL Server 分頁預存程序，sql預存程序

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 = '')BEGIN SET @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