CREATE PROCEDURE dbo.yg_pagination2005
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@strWhere varchar(1500) = '', -- 查询条件 (注意: 不要加 where)
@strOrder varchar(255)='', -- 排序的字段名(注意: 不要加 order by)
@doCount int output
ASdeclare @strSQL varchar(4000)if @strWhere !=''
set @strSQL='SELECT * FROM '
+'(SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrder+') AS pos,'+@strGetFields +' FROM ['+@tblName+'] where (' +@strWhere+'))t1'
+' WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)
else
set @strSQL='SELECT * FROM '
+'(SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrder+') AS pos,'+@strGetFields +' FROM ['+@tblName+'])t1'
+' WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)exec (@strSQL)-- 设置总记录数
if @strWhere !=''
set @strSQL = 'select @var = count(*) from [' + @tblName + '] where (' + @strWhere + ')'
else
set @strSQL = 'select @var = count(*) from [' + @tblName + ']'
exec sp_executesql @strSQL,N'@var int output',@doCount outputreturn @doCount
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) = '*', -- 需要返回的列
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@strWhere varchar(1500) = '', -- 查询条件 (注意: 不要加 where)
@strOrder varchar(255)='', -- 排序的字段名(注意: 不要加 order by)
@doCount int output
ASdeclare @strSQL varchar(4000)if @strWhere !=''
set @strSQL='SELECT * FROM '
+'(SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrder+') AS pos,'+@strGetFields +' FROM ['+@tblName+'] where (' +@strWhere+'))t1'
+' WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)
else
set @strSQL='SELECT * FROM '
+'(SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrder+') AS pos,'+@strGetFields +' FROM ['+@tblName+'])t1'
+' WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)exec (@strSQL)-- 设置总记录数
if @strWhere !=''
set @strSQL = 'select @var = count(*) from [' + @tblName + '] where (' + @strWhere + ')'
else
set @strSQL = 'select @var = count(*) from [' + @tblName + ']'
exec sp_executesql @strSQL,N'@var int output',@doCount outputreturn @doCount
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货