SQL paging query stored procedure Instance code

Don't say much, please look at the code

------------------------------------- -----Author: Zhang Xinyu -----Time: 2013-06-28 -----Profile: Paging queries based on parameters and criteria ------------------------------------- Createproc [dbo]. [Up_pagingquerybyparameter] ( -----table name or SQL statement that can query to the result {SQL statement must have parentheses around: (SELECT * from TBL1)} @TableNamevarchar (max), -----The Column name statement to query; Nullable default * @ColumnNamevarchar (5000), The column-----used to sort; Cannot be empty @OrderByColumnNamevarchar (50), -----Sort desc (reverse 5.4.3.2.1) or ASC (positive sequence 1.2.3.4.5); Nullable default ASC @ShrtByvarchar (4), -----Where condition; Nullable default 1=1 @Wherevarchar (5000), -----display number per page; Nullable default 20 @PageShowsint, -----The current number of pages; Nullable default 1 @CurrentPageint, -----0 is a paging query, others are all inquiries; Nullable default 0 @IsPagingint ) As Begin -----parameter Inspection and specification Ifisnull (@Where, n ') =n ' set@where= n ' 1=1 '; Ifisnull (@ColumnName, n ') =n ' set@columnname = n ' * '; Ifisnull (@PageShows, 0) <1set@pageshows = 20; Ifisnull (@CurrentPage, 0) <1set@currentpage = 1; Ifisnull (@ShrtBy, N ') =n ' Set@shrtby = ' ASC '; Ifisnull (@IsPaging, 0) <>1set@ispaging = 0; -----definition --Concatenation of SQL statements Declare@sqlstr nvarchar (max); Declare@sqlwithstr nvarchar (max); --Number of start bars Declare@startindexint; --End of bar Declare@endindexint; -----Assignment Set@startindex = (@CurrentPage-1) * @PageShows +1; Print (@CurrentPage); Print (@PageShows); Print (@StartIndex); Set@endindex = @CurrentPage * @PageShows; Print (@EndIndex); Set@orderbycolumnname= @OrderByColumnName + ' + @ShrtBy; -----Paged Query Set@sqlwithstr = N ' with temp as (select Row_number () ' + @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), @StartIndex) +n ' and ' +convert (nvarchar), @EndIndex) +n '; ----print (@SqlStr); EXEC (@SqlStr); -----totals 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); End End

--> --> -->