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 注意該存儲過程的第二個參數是定義動態變量，第三個參數開始便是設置動態變量的值。
浙公網安備 33010602011771號