一个通用分页存储过程

  今天把项目中的存储过程抠出来学习了下,发现确实是一个可以通吃全部的分页存储过程。

Create PROCEDURE [dbo].[SqlDataPaging]
 @tbName varchar(255),				--视图名    此处为自己定义的视图 所以该存储过程才能通吃全部  就是不晓得性能如何
 @tbFields varchar(1000) = '*',		--字段名(全部字段为*)
 @orderFiled varchar(5000),			--排序字段(必须!支持多字段)
 @orderType	int,					--排序类型,1是升序,0是降序 
 @strWhere varchar(5000) = Null,	--条件语句(不用加where)
 @pageSize int,						--每页多少条记录
 @pageIndex int = 1,				--指定当前为第几页
 @pageRecord int output				--输出记录总数 
AS
BEGIN
	Begin Tran --开始事务	

    Declare @sql nvarchar(4000)
	Declare @TotalPage int			--返回总页数

	--计算总记录数
	if (@strWhere = '' or @strWhere = NULL)
		set @sql = 'select @pageRecord = count(*) from ' + @tbName
    else
		set @sql = 'select @pageRecord = count(*) from ' + @tbName + '  where ' + @strWhere
	EXEC sp_executesql @sql,N'@pageRecord int OUTPUT',@pageRecord OUTPUT      

    --计算总页数
    select @TotalPage=CEILING((@pageRecord+0.0)/@PageSize)

    if (@strWhere = '' or @strWhere = NULL)
		if (@orderType = 1)
			set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @orderFiled + ') as rowId,' + @tbFields + ' from ' + @tbName
		else
			set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @orderFiled + ' desc) as rowId,' + @tbFields + ' from ' + @tbName 
    else
		if (@orderType = 1)
			set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @orderFiled + ') as rowId,' + @tbFields + ' from ' + @tbName + ' where ' + @strWhere
		else
			set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @orderFiled + ' desc) as rowId,' + @tbFields + ' from ' + @tbName + ' where ' + @strWhere

    --处理页数超出范围情况
    if @PageIndex<=0 
        Set @pageIndex = 1
    
    if @pageIndex>@TotalPage
        Set @pageIndex = @TotalPage

     --处理开始点和结束点
    Declare @StartRecord int
    Declare @EndRecord int
    
    set @StartRecord = (@pageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @pageSize - 1

    --继续合成sql语句
    set @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)
    print @sql 
	exec(@Sql) 
	---------------------------------------------------
    If @@Error <> 0
      Begin
        RollBack Tran
        Return -1
      End
    Else
      Begin
        Commit Tran        
    End 
END