Transact-SQL 示例 - 如何在拼接的動態sql內呼叫外部變數

weixin_34219944發表於2013-06-13
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 注意該儲存過程的第二個引數是定義動態變數，第三個引數開始便是設定動態變數的值。

相關文章