High-efficiency paging stored procedure instance in sql2000/2005

The code is as follows:

Copy code

Create procedure [dbo]. [ProcCustomPage]     ( @ Table_Name varchar (5000), -- table name @ Sign_Record varchar (50), -- primary key @ Filter_Condition varchar (1000), -- filter condition without where @ Page_Size int, -- page size @ Page_Index int, -- page index @ TaxisField varchar (1000), -- sort field @ Taxis_Sign int, -- the sorting method 1 is DESC, and 0 is ASC @ Find_RecordList varchar (1000), -- query field @ Record_Count int -- Total number of records      ) AS BEGIN DECLARE @ Start_Number int DECLARE @ End_Number int DECLARE @ TopN_Number int DECLARE @ sSQL varchar (8000) If (@ Find_RecordList = '') BEGIN SELECT @ Find_RecordList = '*' END SELECT @ Start_Number = (@ Page_Index-1) * @ Page_Size IF @ Start_Number <= 0 SElECT @ Start_Number = 0 SELECT @ End_Number = @ Start_Number + @ Page_Size IF @ End_Number> @ Record_Count SELECT @ End_Number = @ Record_Count SELECT @ TopN_Number = @ End_Number-@ Start_Number IF @ TopN_Number <= 0 SELECT @ TopN_Number = 0 Print @ TopN_Number Print @ Start_Number Print @ End_Number Print @ Record_Count IF @ TaxisField ='' Begin Select @ TaxisField = @ Sign_Record End IF @ Taxis_Sign = 0 BEGIN IF @ Filter_Condition ='' BEGIN SELECT @ sSQL = 'select' + @ Find_RecordList + 'from' + @ Table_Name +' WHERE '+ @ Sign_Record +' in (select top '+ CAST (@ TopN_Number as varchar (10) + ''+ @ Sign_Record + 'from' + @ Table_Name +' WHERE '+ @ Sign_Record +' in (select top '+ CAST (@ End_Number as varchar (10) + ''+ @ Sign_Record + 'from' + @ Table_Name +' Order by '+ @ TaxisField +') order by '+ @ TaxisField + 'desc) order by' + @ TaxisField END ELSE BEGIN SELECT @ sSQL = 'select' + @ Find_RecordList + 'from' + @ Table_Name +' WHERE '+ @ Sign_Record +' in (select top '+ CAST (@ TopN_Number as varchar (10) + ''+ @ Sign_Record + 'from' + @ Table_Name +' WHERE '+ @ Sign_Record +' in (select top '+ CAST (@ End_Number as varchar (10) + ''+ @ Sign_Record + 'from' + @ Table_Name +' WHERE '+ @ Filter_Condition + 'Order by' + @ TaxisField +') and '+ @ Filter_Condition + 'Order by' + @ TaxisField + 'desc) and '+ @ Filter_Condition + 'Order by' + @ TaxisField END END ELSE BEGIN IF @ Filter_Condition ='' BEGIN SELECT @ sSQL = 'select' + @ Find_RecordList + 'from' + @ Table_Name +' WHERE '+ @ Sign_Record +' in (select top '+ CAST (@ TopN_Number as varchar (10) + ''+ @ Sign_Record + 'from' + @ Table_Name +' WHERE '+ @ Sign_Record +' in (select top '+ CAST (@ End_Number as varchar (10) + ''+ @ Sign_Record + 'from' + @ Table_Name +' Order by '+ @ TaxisField + 'desc') order by' + @ TaxisField + ') order by' + @ TaxisField + 'desc' END ELSE BEGIN SELECT @ sSQL = 'select' + @ Find_RecordList + 'from' + @ Table_Name +' WHERE '+ @ Sign_Record +' in (select top '+ CAST (@ TopN_Number as varchar (10) + ''+ @ Sign_Record + 'from' + @ Table_Name +' WHERE '+ @ Sign_Record +' in (select top '+ CAST (@ End_Number as varchar (10) + ''+ @ Sign_Record + 'from' + @ Table_Name +' WHERE '+ @ Filter_Condition + 'Order by' + @ TaxisField + 'desc) and' + @ Filter_Condition + 'Order by' + @ TaxisField + ') and '+ @ Filter_Condition + 'Order by' + @ TaxisField + 'desc' END END EXEC (@ sSQL) IF @ ERROR <> 0 RETURN-3 RETURN 0 END      PRINT @ sSQL GO

Sql2005

The code is as follows:

Copy code

Create procedure [dbo]. [GetRecordFromPage] @ SelectList VARCHAR (2000), -- List of fields to be selected @ TableSource VARCHAR (100), -- table name or View chart @ SearchCondition VARCHAR (2000), -- query condition @ OrderExpression VARCHAR (1000), -- sort expression @ PageIndex INT = 1, -- page number, starting from 0 @ PageSize INT = 10 -- page size AS BEGIN IF @ SelectList is null or ltrim (RTRIM (@ SelectList) ='' BEGIN SET @ SelectList = '*' END PRINT @ SelectList SET @ SearchCondition = ISNULL (@ SearchCondition ,'') SET @ SearchCondition = LTRIM (RTRIM (@ SearchCondition )) IF @ SearchCondition <>'' BEGIN If upper (SUBSTRING (@ SearchCondition, 1, 5) <> 'where' BEGIN SET @ SearchCondition = 'where' + @ SearchCondition END END PRINT @ SearchCondition SET @ OrderExpression = ISNULL (@ OrderExpression ,'') SET @ OrderExpression = LTRIM (RTRIM (@ OrderExpression )) IF @ OrderExpression <>'' BEGIN If upper (SUBSTRING (@ OrderExpression, 1, 5) <> 'where' BEGIN SET @ OrderExpression = 'Order by' + @ OrderExpression END END PRINT @ OrderExpression IF @ PageIndex is null or @ PageIndex <1 BEGIN SET @ PageIndex = 1 END PRINT @ PageIndex IF @ PageSize is null or @ PageSize <1 BEGIN SET @ PageSize = 10 END PRINT @ PageSize DECLARE @ SqlQuery VARCHAR (4000) SET @ SqlQuery = 'select' + @ SelectList + ', RowNumber FROM (SELECT '+ @ SelectList +', ROW_NUMBER () OVER ('+ @ OrderExpression +') AS RowNumber FROM '+ @ TableSource + ''+ @ SearchCondition +') AS RowNumberTableSource WHERE RowNumber BETWEEN '+ CAST (@ PageIndex-1) * @ PageSize + 1) as varchar) + 'And' + CAST (@ PageIndex * @ PageSize) as varchar) -- Order by '+ @ OrderExpression PRINT @ SqlQuery SET NOCOUNT ON EXECUTE (@ SqlQuery) SET NOCOUNT OFF RETURN @ RowCount END