通用sql分页存储过程

--------------------------------
--GetPager
--YuanZhiJun
--2013-2-6
--------------------------------
Create PROC dbo.P_GetPager
@Fields nvarchar(4000),
@TableName nvarchar(50),
@Conditions nvarchar(MAX),
@OrderField nvarchar(50),
@OrderType int=1,--1:asc 2:desc
@PageIndex int=1,
@PageSize int=10,
@TotalRecord int OUTPUT
AS
DECLARE @sqlRecord NVARCHAR(max)
DECLARE @sql NVARCHAR(max)
IF(@Conditions IS NOT NULL AND LTRIM(RTRIM(@Conditions))!='')
BEGIN
    SET @Conditions='WHERE '+@Conditions
END
ELSE
BEGIN
    SET @Conditions='WHERE 1=1'
END
SET @sqlRecord='select @a=count(*) from '+@TableName+' '+@Conditions
EXEC sp_executesql @sqlRecord,N'@a int output',@TotalRecord OUTPUT
--PRINT @sqlRecord
--PRINT @TotalRecord

--exec page
DECLARE @sqlOrder NVARCHAR(100)
IF(@OrderType=1)
BEGIN
SET @sqlOrder='order by '+@OrderField+' asc'
END
ELSE
BEGIN
SET @sqlOrder='order by '+@OrderField+' desc'
END


SET @sql='select ROW_NUMBER() OVER('+@sqlOrder+') AS RowNum,'+@Fields+' from '+@TableName+' '+@Conditions
--PRINT @sql
DECLARE @startIndex INT
DECLARE @endIndex INT
IF(@PageIndex=1)
Begin
SET @startIndex=@PageIndex
SET @endIndex=@PageSize
END
ELSE
BEGIN
SET @startIndex=(@PageIndex-1)*@PageSize+1
SET @endIndex=@startIndex+@PageSize
End

SET @Conditions=' Where RowNum BETWEEN '+CAST(@startIndex AS NVARCHAR(10))+' AND '+CAST(@endIndex AS NVARCHAR(10))

SET @sql='select * from ('+@sql+') as a '+@Conditions
PRINT @sql
EXEC(@sql)