Transact-SQL 示例 - 如何在拼接的动态sql内调用外部变量

任务需求是这样的，定义一个存储过程可以根据设定的存储过程参数去查询某个表返回可以分页的，参数的功能如下：
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'

 

    --若要调试请撤销如下代码的注释并注释<code1/>处

    --SET @rows = 100

    --print @sql

     

    -- <code1>

    EXEC sp_executesql @sql,

        N'@pageNoInner int, @pageSizeInner int, @rowsInner int OUTPUT',

        @pageNoInner = @pageNo,

        @pageSizeInner = @pageSize,

        @rowsInner = @rows OUTPUT

    -- </code1>

END

　　

以下为调用：

?

DECLARE @rows int

exec dbo.Demo1 2, 20, N'', N'object_id asc', @rows out

 

print @rows

实现的原理其实将原来的Exec @sql改用sp_executesql 注意该存储过程的第二个参数是定义动态变量，第三个参数开始便是设置动态变量的值。