SQLServer 分页存储过程

--分页存储过程
alter procedure getPagingTest
	@tblName varchar(20),--分页表名
	@columnPri varchar(200),--分页表主键字段
	@currentPage int,--当前页码(如果@currentPage=0，则取消分页显示，显示所有记录数)
	@pageSize int,--每页记录数
	@totalCount int output,--总记录数(返回总记录)
	@totalPage int output,--总页码(返回总页码)
	@whereStr varchar(500),--查询条件(不带where)
	@orderStr varchar(500)--排序条件(带order by  写法要固定)
as
begin
	declare @sqlStr varchar(2000), --分页最终语句
			@sqlStrC nvarchar(2000) --总记录、总页数语句
	--总记录数
	if ISNULL(@whereStr,'')=''
		set @sqlStrC = N'select @totalCount = count(*) from '+@tblName
	else
		set @sqlStrC = N'select @totalCount = count(*) from '+@tblName + ' where '+@whereStr	
		
	EXEC sp_executesql @sqlStrC,N'@totalCount int output',@totalCount output --sp_executesql：系统过程
	
	--总页码
	set @totalPage = (@totalCount + @pageSize - 1)/@pageSize
	
	--分页
	if @currentPage=0 --显示全部数据	
		begin
			if isnull(@whereStr,'')!=''
				set @sqlStr = 'select * from '+@tblName+' where '+@whereStr+' '+@orderStr
			else
				set @sqlStr = 'select * from '+@tblName + @orderStr
		end
	else		
		begin
			if @currentPage = 1 --第一页显示
				set @sqlStr='select top '+str(@pageSize)+' * from '+@tblName + ' where '+@whereStr + ' '+@orderStr
			else
				begin	
					--利用主键@columnPri>max(主键值)的条件筛选显示页的数据记录
					--去除order by 
					if ISNULL(@orderStr,'')=''
						set @orderStr = ''
					else
						begin
							set @orderStr = REPLACE(@orderStr,'order by',',')
						end						
					set @sqlStr='select top '+str(@pageSize)+' * from '+@tblName + ' where '+@columnPri+' < (select max(cast('+@columnPri+' as varchar(200))) from (select top '+str((@currentPage - 1)*@pageSize)+' from '+@tblName+' order by '+@columnPri+@orderStr+')) order by '+@columnPri+@orderStr+'' 
				end
		end
	exec(@sqlstr)
end