分页存储过程

create proc AAA
(
@PageSize int,
@PageIndex int,
@TotalCount int output
)
as
begin
   declare @Countsql nvarchar(2000)
   declare @Sql nvarchar(2000)
   
   set @Countsql ='select @TotalCount=count(*) from dbo.spt_values'
   execute sp_executesql @Countsql ,N'@TotalCount int output',@TotalCount output--只屏蔽此行时,显示@TotalCount值为NULL
   
   set @Sql = 'select row_number() over(order by name asc) as rownum, spt_values.* from spt_values'
   set @Sql ='select * from ('+@Sql+') as Temp where Temp.rownum >= '
   +CONVERT (nvarchar(200),(@PageIndex - 1)*@PageSize+1)+' and Temp.rownum <='+ CONVERT (nvarchar(200),@PageIndex*@PageSize)
   
   execute sp_executesql @Sql 
   select @TotalCount --只屏蔽此行时,结果中不显示@TotalCount总行数,只有分页的结记录果集
end


-------第一种执行存储过程的方法----------

declare @PSize int
declare @PIndex int
declare @TCount int
set @PSize =10
set @PIndex  = 4
execute AAA @PSize,@PIndex,@TCount  output

-------第二种执行存储过程的方法-----------
declare @TCount int
execute AAA 10,4,@TCount  output


注释：

sp_executesql为系统的存储过程，在执行动态拼接的sql语句时，需要调用

当执行第二种方法时，参数的顺序为存储过程中声明的顺序

set @Countsql ='select @TotalCount=count(*) from dbo.spt_values'
execute sp_executesql @Countsql ,N'@TotalCount int output',@TotalCount output
执行Countsql语句时，由于含有一个参数，必须在执行时用N来表明，，N'@TotalCount int output'

 

菜鸟一只，不对的地方望高手指出，谢谢！