if object_id('[Proc_selectforpager]') is not NULL Drop Procedure [Proc_selectforpager]GoCreate ProcProc_selectforpager (@Tb_name varchar(Max) ,--Table name @Order varchar(4000) ,--Sort Fields @CurrentPage int,--Current Page @Field varchar( -),--query Fields @PageSize int,--shows the number of records per page @TotalCount intoutput) asDeclare @Exec_sql nvarchar(Max)/*total number of records queried*/ Set @Exec_sql='Set @TotalCount = (Select Count (1) from'+@Tb_name+'As a)'Execsp_executesql@Exec_sqlN'@TotalCount int Output',@TotalCountOutput/*Set Sort field*/Set @Order=IsNull('Order by'+Nullif(@Order,"'),'Order by getdate ()')/*Paging Query*/ Set @Exec_sql=' ; With CTE as (Select *,row_number () over ('+@Order+') as R from (Select * from'+@Tb_name+') as a) Select'+@Field+'From CTE Where R between (@CurrentPage-1) * @pagesize +1 and @CurrentPage * @pagesize Order by R' Execsp_executesql@Exec_sqlN'@CurrentPage int, @PageSize int',@CurrentPage,@PageSize Go
SQL Server Universal Paging stored procedures