-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE P_GetPager
@tableName NVARCHAR(100),
@fields NVARCHAR(200),
@where NVARCHAR(200),
@orderBy NVARCHAR(100),
@pageIndex INT =1,
@pageSize INT =20,
@rowCount INT=0 OUTPUT
AS
BEGIN
IF (@pageIndex<1)set @pageIndex=1
DECLARE @between NVARCHAR(100);
SET @between= CONVERT( NVARCHAR, (@pageIndex-1)*@pageSize+1) +' and ' + CONVERT( NVARCHAR, @pageIndex*@pageSize);
DECLARE @sql nvarCHAR(4000);
SET @sql='SELECT '+@fields+' INTO #temp FROM '+@tableName+' WHERE '+@where+';';
SET @sql =@sql +'select @rowCount=count(*) from #temp;'
SET @sql =@sql +' select '+@fields+' from (SELECT ROW_NUMBER () OVER (order by '+@orderBy+' DESC) AS rowno ,'+@fields+' FROM #temp) TEMPLATE WHERE rowno BETWEEN '+@between+''
EXECUTE sp_executesql @sql,N'@rowCount INT OUTPUT',@rowCount OUTPUT
END
GO