Transact-SQL示例

任务需求是这样的，定义一个存储过程可以根据设定的存储过程参数去查询某个表返回可以分页的，参数的功能如下： 1.返回记录集的第n页（@pageNo） 2.每页显示n条记录（@pageSize） 3.筛选条件（@where） 4.排序规则（@orderby） 5.总记录数（@rows） Ok,在拿

任务需求是这样的，香港虚拟主机，定义一个存储过程可以根据设定的存储过程参数去查询某个表返回可以分页的，参数的功能如下：
1.返回记录集的第n页（@pageNo）
2.每页显示n条记录（@pageSize）
3.筛选条件（@where）
4.排序规则（@orderby）
5.总记录数（@rows）

Ok,在拿到需求后，香港服务器，便开始分析。。。这里需要使用到拼接sql字符串变量然后Exec @sql即可，美国空间，但是实践发现。Exec @sql并不能把总记录数传递给外部的@rows OUTPUT参数上，为此stackoverflow一番发现答案。

下面是本人目前的做法：

--定义存储过程 -- 示例过程中使用每个数据库都会自带的系统视图sys.objects作为测试用表 CREATE PROCEDURE dbo.Demo1 @pageNo int = 1, @pageSize int = 10, @where nvarchar(1000) = N'', @orderby nvarchar(1000) = N'name asc', @rows int OUTPUT AS BEGIN DECLARE @sql nvarchar(max) SET @sql = N'SELECT @rowsInner = COUNT(1) FROM sys.objects' IF @where <> N'' SET @sql = @sql + N' WHERE ' + @where SET @sql = @sql + N'; SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY ' + @orderby + N') RowId, object_id, name FROM sys.objects ' IF (@where <> N'') SET @sql = @sql + N'WHERE ' + @where SET @sql = @sql + N' ) a WHERE a.RowId between (@pageNoInner - 1) * @pageSizeInner + 1 AND @pageNoInner * @pageSizeInner' --若要调试请撤销如下代码的注释并注释处 --SET @rows = 100 --print @sql -- EXEC sp_executesql @sql, N'@pageNoInner int, @pageSizeInner int, @rowsInner int OUTPUT', @pageNoInner = @pageNo, @pageSizeInner = @pageSize, @rowsInner = @rows OUTPUT -- END