請教如何透過sp_executesql來取得動態sql的回傳值

mssql t-sql sp_executesql

Doggww 2021-12-07 18:03:53 ‧ 2810 瀏覽

分享至

Hi 各位先進好,

小弟目前嚐試建立一function,其主要行為如下:
1.動態產生取值的sql (因為from的table是不固定的,需由function的傳入值來組合出要selec的table,ps.該table是有命名規則的,故理論上是會存在的.但是其table是會一直增加的,故無法事先全部表列出來)
2.透過sp_executesql來執行上述sql,並取得回傳值
3.該function再return 上述的回傳值

本人對sp_executesql用法的理解如下
sp_executesql [動態sql],[動態sql內的變數宣告],[動態sql內的變數與外部變數或值的對映,.....]

以下是我的function code
Create FUNCTION [dbo].[ZZ_ActiveID_Get_ApplyNo]
(
@ActiveID int
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Result nvarchar(4000),
@FlowId int,
@TableName nvarchar(50),
@SQL NVARCHAR(MAX)
--
SELECT @FlowId = A.flowid,
@TableName = 'Flow'+CONVERT(NVARCHAR,A.flowid)+'_Activity'
FROM Activity A
WHERE A.ActiveID = @ActiveID;

--
IF OBJECT_ID(@TableName) IS NOT NULL 
  BEGIN	  
    SET @SQL = 'SELECT @t1 = ApplyNo ' +
	           '  FROM dbo.' + @TableName + 
			   ' Where ActiveID = ' + CAST(@ActiveID AS NVARCHAR) + ';'
    EXEC sp_executesql @SQL,N'@t1 NVARCHAR(4000) OUT',@Result OUT
  END
ELSE
  SET @Result = NULL

Return @Result;

END

其執行以下sql驗證:
select dbo.ZZ_ActiveID_Get_ApplyNo(244150)

但卻回出現以下錯誤訊息><
訊息 557，層級 16，狀態 2，行 1
只有函數和一些擴充預存程序可以從函數內執行。

請問各位先進是否小弟對 sp_executesql 的使用方法有所誤解><

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

純真的人

iT邦大師 1 級 ‧ 2021-12-07 18:19:40

你先參考看看~我之前寫的發文~裡面有取回傳值的方式

SQL模擬資料匯出及印出新增資料插入~
https://ithelp.ithome.com.tw/articles/10282812

declare @i int=0,@Count int=0
declare @TargetID int,@TargetName nvarchar(50)=''
declare @SQLCommand nvarchar(max)
declare @TableName nvarchar(50) = N'Test',@MainKey nvarchar(50) = 'Test_ID',@StrName nvarchar(max),@SetStr nvarchar(max)

-- 紀錄變數資料表有幾筆
declare @CountTable nvarchar(max) = 'select @CountOut=Count(0) from ' + @TableName
declare @CountSetOut nvarchar(max) = '@CountOut int OUTPUT'
exec sp_executesql @CountTable,@CountSetOut,@CountOut=@Count OUTPUT

測試了一下~function裡面放了sp_executesql一定報錯
所以function不能使用sp_executesql預存程序
那你可能要改用Procedure來處理了~