SQL paging query Stored Procedure Code sharing, SQL stored procedure

SQL paging query Stored Procedure Code sharing, SQL stored procedure

For more information, see the code.

---------------------------------------- Author: Zhang Xinyu ----- time: 2013-06-28 ----- Introduction: query by parameters and conditions by PAGE ------------------------------------- Create proc [dbo]. [Up_PagingQueryByParameter] (----- the table name or the SQL statement that can query the result. {the SQL statement must have parentheses (select * from tbl1)} @ TableName varchar (max ), ----- statement of the column name to be queried; can be empty default * @ ColumnName varchar (5000), ----- Column Used for sorting; cannot be blank @ OrderByColumnName varchar (50 ), ----- sort desc (Reverse Order 5.4.3.2.1) or asc (Forward order 1.2.3.4.5); can be empty default asc @ ShrtBy varchar (4), ----- Where condition; optional values: 1 = 1 @ Where varchar (5000); ----- number of entries displayed on each page; optional values: 20 @ PageShows int, ----- current page number; default value: 1 @ CurrentPage int, ----- 0 is a paging query; others are all queries. if null, the default value 0 @ IsPaging int) asbegin ----- parameter check and if isnull (@ Where, n '') = n'set @ Where = n' 1 = 1'; if isnull (@ ColumnName, n') = n' set @ ColumnName = n '*'; if isnull (@ PageShows, 0) <1 set @ PageShows = 20; if isnull (@ CurrentPage, 0) <1 set @ CurrentPage = 1; if isnull (@ ShrtBy, n') = n' set @ ShrtBy = 'asc '; if isnull (@ IsPaging, 0) <> 1 set @ IsPaging = 0; ----- define -- concatenate the SQL statement declare @ SqlStr nvarchar (max); declare @ SqlWithStr nvarchar (max); -- start number declare @ StartIndex int; -- end number declare @ EndIndex int; ----- value set @ StartIndex = (@ CurrentPage-1) * @ PageShows + 1; print (@ CurrentPage); print (@ PageShows); print (@ StartIndex ); set @ EndIndex = @ CurrentPage * @ PageShows; print (@ EndIndex); set @ OrderByColumnName = @ OrderByColumnName + ''+ @ ShrtBy; ----- set @ SqlWithStr = N' with temp as (select ROW_NUMBER () over (order by '+ @ OrderByColumnName + N') as RowNumber, '+ @ ColumnName + N' from' + @ TableName + N' as tableName where '+ @ Where + N')'; if (@ IsPaging = 0) begin set @ SqlStr = @ SqlWithStr + N 'select' + @ ColumnName + N' from temp where temp. rowNumber between' + convert (nvarchar (20), @ StartIndex) + N' and '+ convert (nvarchar (20), @ EndIndex) + N ''; ---- print (@ SqlStr); exec (@ SqlStr); ----- total number query set @ SqlStr = @ SqlWithStr + N' select count (*) as TotalNumber from temp '; ---- print (@ SqlStr); exec (@ SqlStr); end else begin set @ SqlStr = @ SqlWithStr + n'select' + @ ColumnName + n'from temp '; ---- print (@ SqlStr); exec (@ SqlStr); endend

The above is all the content of this article. I hope this article will help you in your study or work. I also hope to provide more support to the customer's home!