![](/icons/2433de.gif)
![](/icons/2433dou.gif)
![](/icons/2433yi.gif)
![](/icons/2433de.gif)
![](/icons/2433de.gif)
![](/icons/2433dou.gif)
![](/icons/2433dou.gif)
![](/icons/2433dou.gif)
![](/icons/2433dou2.gif)
我先发
![](/icons/2433yi.gif)
![](/icons/2433de.gif)
![](/icons/2433dou.gif)
![](/icons/2433dou.gif)
![](/icons/2433dou2.gif)
="code_img_closed" src="http://CrazyCoder.cn/WebFiles/200912/33acc47f-0742-498f-b694-0359aebe62ea.g
![](/icons/2433if.gif)
![](/icons/2433class.gif)
![](/icons/2433de.gif)
--@TableName 表名(可做联合查询)
--@Condition 查询条件
--@OrderBy 排序规则
--@PageNum 第几页
--@PageSize 每页有多少务记录
--@PageCount 输出总页数
--@RecordCount 输出总记录数Create Procedure proc_CurrencyPage
(
@Columns varchar(max),
@TableName varchar(max),
@Condition varchar(max),
@OrderBy varchar(max),
@PageNum
![](/icons/2433int.gif)
@PageSize
![](/icons/2433int.gif)
@PageCount
![](/icons/2433int.gif)
@RecordCount big
![](/icons/2433int.gif)
)AS
DECLARE @Sql nvarchar(max);
Set @Sql = 'Select @CountOut = Count(*) From ' + @TableName + ' Where ' + @Condition;
EXEC sp_executesql @Sql,N'@CountOut INT OUTPUT',@CountOut = @RecordCount OUTPUT;
Set @PageCount = @RecordCount / @PageSize;
IF(@RecordCount % @PageSize > 0) Set @PageCount = @PageCount + 1;
IF(@PageNum < 1) Set @PageNum = 1;
IF(@PageNum > @PageCount) Set @PageNum = @PageCount;
Declare @BRowNum
![](/icons/2433int.gif)
![](/icons/2433int.gif)
Set @BRowNum = (@PageNum - 1) * @PageSize;
Set @ERowNum = @BRowNum + @PageSize;
Set @Sql =
'Select * From (
Select '+@Columns+', ROW_NUMBER
![](/icons/2433kh.gif)
From ' + @TableName + '
Where ' + @Condition + '
) as TempT
Where RowNum > ' + Convert(varchar(10),@BRowNum) + '
And
RowNum <= ' + Convert(varchar(10),@ERowNum) + '
Order By ' + @OrderBy;
Exec(@Sql);GO
--测试:Declare @Columns varchar(max)Declare @TableName varchar(max)Declare @Condition varchar(max)Declare @OrderBy varchar(max)Declare @PageNum
![](/icons/2433int.gif)
![](/icons/2433int.gif)
![](/icons/2433int.gif)
![](/icons/2433int.gif)
![](/icons/2433set.gif)
![](/icons/2433set.gif)
![](/icons/2433set.gif)
![](/icons/2433set.gif)
![](/icons/2433set.gif)
![](/icons/2433set.gif)
Exec proc_CurrencyPage @Columns,@TableName,@Condition,@ORDERBY,@PageNum,@PageSize,@PageCount output,@RecordCount output
Tag标签: 分页,存储过程,SqlServer2005
最新评论