SQL Server database stored procedure page

1. Set quoted_identifier off
2. Go
3. Set ansi_nulls off
4. Go
6. Alter procedure cms_searchgetdatabypage
7. @ Tblname varchar (255), -- table name
8. @ Fldnames varchar (1000), -- the list of selected fields is separated by commas (,).
9. @ Selectorderfldname varchar (500), -- Sort fields are separated by commas (,) (fields specified by keyfldname cannot be included and can be blank)
10. @ Orderflddesc varchar (500), -- sorting field and sorting direction, such as adddate DESC and id desc (the sorting field must be specified by selectorderfldname and can be empty)
11. @ KeyFldName varchar (255), -- primary key field
12. @ PageSize int = 10, -- page size
13. @ PageIndex int = 1, -- page number
14. @ StrWhere varchar (1000) = ''-- Query condition (Note: Do not add where)
15. AS
16. Declare @ strWhereA varchar (1200) -- temporary variable, add where to sqlwhere
17. Declare @ strOrderA varchar (2000) -- first sorting type
18. Declare @ strorderb varchar (2000) -- Second sorting type
19. Declare @ strsqla varchar (4000) -- selected for the first time
20. Declare @ strsqlb varchar (8000) -- selected for the second time
21. Declare @ strsql varchar (8000) -- Final selection
22. /* Condition */
23. If @ strwhere! =''
24. Set @ strwherea = 'where' + @ strwhere
25. Else
26. Set @ strwherea =''
28. /* Select the field list */
29. If @ fldnames is null or rtrim (@ fldnames) =''
30. Set @ fldnames = '*'
32. /* List of sorting fields */
33. If not (@ selectorderfldname is null or rtrim (@ selectorderfldname) = '')
34. If rtrim (@ selectorderfldname) = 'id'
35. Set @ selectorderfldname =''
36. Else
37. Set @ selectorderfldname = ',' + @ selectorderfldname
39. /* Construct an order and sort by the specified Method */
40. If @ orderFldDesc is null or rtrim (@ orderFldDesc) =''
41. Set @ orderFldDesc = 'order by id desc'
42. Else
43. Set @ orderFldDesc = 'ORDER BY' + @ orderFldDesc
44. Set @ strOrderA = UPPER (@ orderFldDesc)
45. Set @ strOrderB = replace (@ strOrderA, 'desc', 'desc1 ')
46. Set @ strOrderB = replace (@ strOrderB, 'asc ', 'desc ')
47. Set @ strOrderB = replace (@ strOrderB, 'sc1', 'asc ')
49. /* Page 1 */
50. If @ PageIndex = 1
51. Set @ strSQL = 'select top '+ str (@ PageSize) + ''+ @ fldNames +' from ['+ @ tblName +'] with (nolock) '+ @ strWhereA + ''+ @ strOrderA
52. Else
53. Begin
54. -- Retrieve the total number of records
55. Declare @ SQL nvarchar (500)
56. Declare @ maxCount int
57. Declare @ maxPage int
58. Declare @ tempRowCount int
59. Set @ SQL = 'select @ maxcount = count ('+ @ keyfldname +') from ['+ @ tblname +'] '+ @ strwherea
60. Exec sp_executesql @ SQL, n' @ maxcount int output', @ maxcount output
61. Set @ maxpage = @ maxcount/@ pagesize
62. If (@ maxcount % @ pagesize> 0)
63. Set @ maxpage = @ maxpage + 1
64. /* Last page */
65. If @ pageindex >=@ maxpage
66. Begin
67. Set @ PageIndex = @ maxPage
68. Set @ strSqlA = char (13) + '(select top' + str (@ maxCount % @ PageSize) + ''+ @ keyFldName + @ selectOrderFldName + 'from [' + @ tblName + '] as a with (nolock)' + @ strWhereA + @ strOrderB + ') '+ char (13)
69. Set @ strSqlB = char (13) + '(select' + @ keyFldName + 'from' + @ strSqlA + 'as B) '+ char (13)
70. Set @ strSQL = 'select' + @ fldNames + 'from [' + @ tblName + '] where ([' + @ keyFldName + '] in' + @ strSqlB + ') '+ @ strOrderA + char (13)
71. End
72. Else
73. Begin
74. /* Not the first page or the last page */
75. If (@ PageIndex <= @ maxPage/2)
76. Begin
77. -- First Half of the pages
78. Set @ tempRowCount = @ PageIndex * @ PageSize
79. /* Construct an SQL statement. This paging algorithm aims to achieve efficient sorting of non-primary keys. By tony */
80. /* 1. Select perPage * pageNum records in descending order based on the specified field + primary key field */
81. Set @ strSqlA = char (13) + '(select top' + str (@ tempRowCount) + ''+ @ keyFldName + @ selectOrderFldName + 'from [' + @ tblName + '] as a with (nolock)' + @ strWhereA + @ strOrderA + ') '+ char (13)
82. /* 2. Select perPage records from the selected records in ascending order */
83. Set @ strSqlB = char (13) + '(select top' + str (@ PageSize) + ''+ @ keyFldName + 'from' + @ strSqlA + 'as B '+ @ strOrderB +') '+ char (13)
84. /* 3. Select the records of the primary key in the record selected for the second time from the database, sort them in descending order, and complete the query by page */
85. Set @ strSQL = 'select' + @ fldNames + 'from [' + @ tblName + '] where ([' + @ keyFldName + '] in' + @ strSqlB + ') '+ @ strOrderA
86. End
87. Else
88. Begin
89. -- The last half of the pages
90. Set @ temprowcount = @ maxcount-(@ pageindex-1) * @ pagesize
91. /* Construct an SQL statement. This paging algorithm aims to achieve efficient sorting of non-primary keys. By Tony */
92. /* 1. Select perpage * pagenum records in descending order based on the specified field + primary key field */
93. Set @ strSqlA = char (13) + '(select top' + str (@ tempRowCount) + ''+ @ keyFldName + @ selectOrderFldName + 'from [' + @ tblName + '] as a with (nolock)' + @ strWhereA + @ strOrderB + ') '+ char (13)
94. /* 2. Select perPage records from the selected records in ascending order */
95. Set @ strSqlB = char (13) + '(select top' + str (@ PageSize) + ''+ @ keyFldName + 'from' + @ strSqlA + 'as B '+ @ strOrderA +') '+ char (13)
96. /* 3. Select the records of the primary key in the record selected for the second time from the database, sort them in descending order, and complete the query by page */
97. Set @ strSQL = 'select' + @ fldNames + 'from [' + @ tblName + '] where ([' + @ keyFldName + '] in' + @ strSqlB + ') '+ @ strOrderA
98. End
99. End
100. End
102. Set nocount on
104. /* Print @ strSQL */-- display SQL
105. Exec (@ strSQL)
106. Set nocount off
107. RETURN
108. GO
109. Set quoted_identifier off
110. Go
111. Set ansi_nulls on
112. Go