超级强大的 分页Sql存储过程

CREATE PROCEDURE [dbo].[GetPage]
 @StrSelect varchar(max) = null,  --欲显示的列（多列用逗号分开），例如：id,name
    @StrFrom varchar(max) = null,  --表名称，或者是表连接字符串，多表连接例如:student as s inner join dwinfo as dw on s.dwbh=dw.bh
    @StrWhere varchar(max) = null,  --查询条件,null代表没有条件，单条件或者多条件，多条件例如：name='啊' and id=10
    @StrOrder varchar(max) = null,  --排序列(多个排序列用逗号分开），例如:id desc,name as
    @ItemCount bigint = 0 output,  --总记录数
    @PageSize int = 50,     --每页显示条数
 @PageIndex int = 1     --当前页

AS
BEGIN
    SET NOCOUNT ON;
    Declare @sql nvarchar(4000);
    Declare @totalRecord int;
    if (@StrWhere ='''' or @StrWhere='' or @StrWhere is NULL)
    set @sql = 'select @totalRecord = count(*) from ' + @StrFrom
    else
    set @sql = 'select @totalRecord = count(*) from ' + @StrFrom + ' where ' + @StrWhere
    EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@ItemCount OUTPUT
    declare @SqlQuery varchar(max)
if(@PageIndex=1)
    begin
  if(@StrWhere is null)
  set @SqlQuery='select top '+convert(varchar,@PageSize)  + ' row_number() over(order by '+@StrOrder+' ) as RowNumber,'+@StrSelect+' from '+@StrFrom;
        else
  set @SqlQuery='select top '+convert(varchar,@PageSize) + ' row_number() over(order by '+@StrOrder+' ) as RowNumber,'+@StrSelect+' from '+@StrFrom+' where '+@StrWhere;
 end
else
 begin
  if(@StrWhere is null)
  begin
   set @SqlQuery='with cte as (select row_number() over(order by '+@StrOrder+' ) as RowNumber,'+@StrSelect+' from '+@StrFrom+')select * from cte where RowNumber between '+
   convert(varchar,((@PageIndex-1)*@PageSize)+1)+' and '+
   convert(varchar,@PageIndex*@PageSize)
  end
  else
  begin
   set @SqlQuery='with cte as (select row_number() over(order by '+@StrOrder+' ) as RowNumber,'+@StrSelect+' from '+@StrFrom+' where '+@StrWhere+') select * from cte where RowNumber between '+
   convert(varchar,((@PageIndex-1)*@PageSize)+1)+' and '+
   convert(varchar,@PageIndex*@PageSize)
  end
 end
 print @SqlQuery
 exec (@SqlQuery)
END

insert into TBjhMovie values(newid(),'视频一','123','信息')

select *from tbjhmovie

declare @pageCount bigint ;
exec [GetPage] 'movieName,movieMessage','[TBjhMovie]',null,'movieName desc',@pageCount,2,1
select @pageCount

 

------------------------------------------此存储过程有单眼皮小猪 曹春辉 编写。--------------------------------