iT邦幫忙

0

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

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 的使用方法有所誤解><

1 個回答

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來處理了~
https://ithelp.ithome.com.tw/upload/images/20211208/20061369JEZEH0h4Ps.png

參考Procedure跟function比較
https://dotblogs.com.tw/von_blog/2016/08/18/160129

更新~

我要發表回答

立即登入回答