SQL Server universal Paging stored procedures

SQL Server2005 Universal Paging stored procedure

CREATE PROCEDURE [dbo]. [Common_getpagedlist] (@TableName nvarchar (100),--table name @ColumnNames nvarchar (1000) = ' * ',--field name collection (All fields are *, other commas separated) @OrderClause nvarchar (1000),- -sort clause (does not include order by) @WhereClause nvarchar (=n ' 1=1 '),--conditional clauses (not including where) @PageSize int = 0,--Number of records per page (0 for all) @PageIndex int = 1,--page index (starting from 1) @TotalRecord int output--returns total records) as

BEGIN if (@ColumnNames is null or @ColumnNames = ") Set @ColumnNames = ' * ' if (@WhereClause is null or @WhereClause = ') Set @WhereClause = ' 1=1 ' if (@OrderClause is null or @OrderClause = ') Set @OrderClause = ' Id desc '

--processing start point and end point Declare @StartRecord int; Declare @EndRecord int; Declare @TotalCountSql nvarchar (1200);   Declare @SqlString nvarchar (4000);

--Statistics record if (@TotalRecord is null OR @TotalRecord >=0) begin SET @TotalCountSql = N ' Select @TotalRecord = count (*) from ' + @TableName + ' where ' [email protected]; --select @TotalCountSql EXEC sp_executesql @totalCountSql, N ' @TotalRecord int out ', @TotalRecord output;--returns the total number of records end

If @PageSize >0 begin If @PageIndex <1 set @PageIndex =1 Set @StartRecord = (@PageIndex-1) * @PageSize + 1 Set @EndRecord = @StartRecord + @PageSize-1 Set @SqlString = N ' Select Row_number () over (order by ' + @OrderClause + ') as rowId, ' [email protected]+ ' from ' + @TableName + ' where ' [email protected]; Set @SqlString = ' select * FROM (' + @SqlString + ') as T where rowId between ' + LTrim (str (@StartRecord)) + ' and ' + Ltri M (str (@EndRecord)); End else begin set @SqlString = ' SELECT ' [email protected]+ ' from ' + @TableName + ' where ' [email protected] + ' ORDER BY ' ' [email protected] End--select @SqlString Exec (@SqlString) End

SQL Server universal Paging stored procedures