SQL Server paging stored procedures, SQL stored procedures

SQL Server paging stored procedures, SQL stored procedures

USE [ReportServerTempDB] gocreate procedure [dbo]. [SeachTablePage] (@ TableName VARCHAR (200), -- table name @ Fileds VARCHAR (500), -- query field @ OrderFiled VARCHAR (100), -- Sort field @ IsDesc BIT, -- whether to sort in descending order @ WhereString VARCHAR (2000), -- query field @ PageIndex INT, -- current page number @ PageSize INT, -- number of entries per page @ TotalRecord int output -- total number of entries returned) ASBEGINDECLARE @ OrderString VARCHAR (500) IF (@ PageIndex is null or @ PageIndex <= 0) BEGINSET @ PageIndex = 1 ENDIF (@ PageSize is null or @ PageSize <= 0) BEGINSET @ PageSize = 10 ENDDECLARE @ StartRowID INTDECLARE @ EndRowID INTSET @ StartRowID = (@ PageIndex-1) * @ PageSize + 1 SET @ 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 -- total number of returned records 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