SQL Server paging stored procedure

SQL Server paging stored procedure

CREATE proc [dbo]. [Proc_opinion_baseinfo]

@TableName varchar (4000),

@PkField varchar (100),

@PageIndex Int=1,

@PageSize int=10,

@SqlWhere nvarchar (4000),

@RowCount bigint OUTPUT,

@PageCount bigint output

As

if (@SqlWhere = ' 1 ')

Set @SqlWhere = ' 1=1 '

declare @sql nvarchar (4000), @start int, @end int

Set @sql = ' SELECT * FROM (select Row_number () + @PkField + ' desc] rowid,* from ' + @TableName + ' where ' + @SqlWher E

Set @start = (@PageIndex-1) * @PageSize +1

Set @end = @start + @PageSize-1

Set @sql = @sql + ') t where rowId between ' +cast (@start as varchar) + ' and ' +cast (@end as varchar (20))

EXEC (@sql)

Set @sql = ' Select @RowCount =count (1) from ' + @TableName + ' where ' + @SqlWhere

EXEC sp_executesql @sql, N ' @RowCount bigint output ', @RowCount output

if (@RowCount% @PageSize =0)

Begin

Set @PageCount = @RowCount/@PageSize

End

Else

Begin

Set @PageCount = @RowCount/@PageSize +1

End