mssql查询分页存储过程SpPageOrder)

CREATE PROCEDURE [SpPageOrder]

@tblName varchar(255),<wbr><wbr> -- 表名<br> @fldName varchar(255),<wbr><wbr> -- 显示字段名<br> @OrderfldName varchar(255), -- 排序字段名<br> @PageSize int = 10,<wbr><wbr> -- 页尺寸<br> @PageIndex int = 1,<wbr><wbr> -- 页码<br> @IsReCount bit = 0,<wbr><wbr> -- 返回记录总数, 非 0 值则返回<br> @OrderType bit = 0,<wbr><wbr> -- 设置排序类型, 非 0 值则降序<br> @strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)<br> AS</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100)<wbr><wbr> -- 临时变量(查询条件过长时可能会出错，可修改100为1000)<br> declare @strOrder varchar(400) -- 排序类型</wbr></wbr>

if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @OrderfldName +' desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @OrderfldName +' asc'
end

set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
+ @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '(['
+ @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @OrderfldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder

if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
+ @tblName + '] where [' + @OrderfldName + ']' + @strTmp + '(['
+ @OrderfldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @OrderfldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder

if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere

set @strSQL = 'select top ' + str(@PageSize) + ' ' + @fldName + ' from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end

if @IsReCount != 0
set @strSQL = @strSQL+' select count(1) as Total from [' + @tblName + ']'

if @strWhere!=''
set @strSQL = @strSQL+' where ' + @strWhere
exec (@strSQL)
GO
----调用方法，调用后生成两张表第一张表中有数据，第二张表就有全部记录数 ds.Tables[1].Rows[0]["Total"];
SpPageOrder booktype,'*',typeid ,4 ,1 ,1 ,1