如何呼叫遠端使用者定義函式？

我有一個 SQL 伺服器，它包含一個到另一個伺服器的連結（例如：測試）我在連結伺服器中有一個函式（name:getvalue(@parameter)）。所以我想在 SQL server 2012 中檢索這個函式輸出。我正在使用以下方法。

DECLARE @sql nvarchar(4000);
DECLARE @somevalue nvarchar(16)

SET @sql = 'SELECT * FROM OPENQUERY(test, 
                         ''SELECT @somevalue=getvalue(' +CAST('545454545' as nvarchar(10))+ ')'')';
EXEC (@sql)

我收到類似的錯誤

連結伺服器“test”的 OLE DB 提供程序“OraOLEDB.Oracle”返回消息“ORA-00936：缺少表達式”。消息 7321，級別 16，狀態 2，第 1 行準備查詢“SELECT @somevalue=getvalue(545454545)”以針對連結伺服器“測試”的 OLE DB 提供程序“OraOLEDB.Oracle”執行時發生錯誤。

請幫助我。在此先感謝

您的遠端查詢嵌套了兩次，首先在 OPENQUERY 中，然後在 EXEC 中。該@somevalue變數是在最外層聲明的，它根本不存在於遠端查詢的範圍內，因此引用無效。

但是您也不能在那裡使用變數，原因有兩個：

1. 首先，正如Colin ’t Hart 所指出的，您的連結伺服器是 Oracle。語法是 SQL Server ，SELECT @var = function(...)在 Oracle 中無效。
2. OPENQUERY 行集函式允許您僅將遠端端的結果視為行集——它並非旨在填充變數，無論是沿途還是作為行集的替代方案。因此，即使@var在基礎查詢中允許類似引用（例如，如果遠端伺服器是 SQL Server），使用 OPENQUERY 您仍然無法在遠端查詢的範圍內引用外部（“this”端的）變數，也如果您想在遠端查詢中使用它們，外部範圍是否能夠訪問在內部範圍（“那個”端）聲明的變數。

到此結束，從 SQL Server 中的 Oracle 標量函式獲取結果的最佳方法可能是讓遠端查詢將結果作為行集返回，同時使用像 OPENQUERY 這樣的行集函式呼叫遠端查詢。從 OPENQUERY 獲取行集後，您可以使用 SQL Server 允許您執行的任何操作，包括將其列值儲存到變數中。因此，您的 OPENQUERY 查詢基本上可以這樣：

SELECT
 **@somevalue =** somecolumn
FROM
 OPENQUERY(
   'test',
   '*your Oracle query*'
 )
;

注意@somevalue =- 它的位置向上移動了一級，現在它是有效的語法並且有意義。這somecolumn將是您分配給返回結果的 Oracle 查詢的單個列的名稱。這可能是您的 Oracle 查詢的樣子：

SELECT getvalue(*argument*) AS somecolumn FROM DUAL

現在，如果您不需要參數化*argument*，完整的 Transact-SQL 查詢可能如下所示：

DECLARE @somevalue nvarchar(16);

SELECT
 @somevalue = myresult
FROM
 OPENQUERY(
   test, 
   'SELECT getvalue(545454545) AS myresult FROM DUAL'
 )
;

但是由於您希望能夠對參數進行參數化，因此需要使SELECT FROM OPENQUERY查詢動態化：將其建構為字元串：

DECLARE @sql nvarchar(max);

…

SET @sql =
N'SELECT
 …
FROM
 OPENQUERY(
   test, 
   ''SELECT getvalue(' + CAST(@someargument AS nvarchar(16)) + N') AS myresult FROM DUAL''
 )
';

然後執行字元串：

EXEC (@sql);

你已經在做的所有事情。但是，當您使用該EXEC (...)語法時，您不能直接將結果接收到變數1中，您只能將其作為結果集獲取——是的，與 OPENQUERY 的限制相同。但是，與 OPENQUERY 不同的是，您不能在 FROM 子句中呼叫 EXEC。

因此，此時您仍然面臨將結果getvalue放入變數的問題。

基本上，有兩種方法可以解決。首先是@somevalue =從動態查詢中刪除該位，以讓 OPENQUERY 的結果集傳遞到最外層範圍。雖然你做不到... FROM EXEC (...)，但你可以做到INSERT ... EXEC (...)。因此，將 EXEC 的結果集插入到臨時數據集中（如表變數）：

DECLARE @tmpresult TABLE (result nvarchar(16));

INSERT INTO @tmpresult (result)
EXEC (@sql);

然後將列值從臨時儲存中讀取到標量變數中：

SELECT @somevalue = result FROM @tmpresult;

另一個，在我看來，更好的選擇是使用EXEC sp_executesql而不是EXEC ()執行動態查詢。使用sp_executesql系統儲存過程，您可以獲得基礎查詢的參數，包括輸出參數。輸出參數正是您如何將結果傳遞到您@somevalue在主腳本中聲明的變數中的方式。

因此，保留@somevalue =動態查詢的一部分，但在呼叫查詢時而不是在查詢中聲明它。這樣，該變數不僅可以用作局部變數，還可以用作參數。包含OUTPUT在參數聲明中也會使其成為輸出參數。所以，這就是你的方式：

SET @sql =
N'SELECT
 **@tmpvalue** = myresult
FROM
 …
';

EXEC sp_executesql @sql, **'@tmpvalue nvarchar(16) OUTPUT'**, @somevalue OUTPUT;

您可以在上面的程式碼中看到我在動態查詢中稍微更改了變數的名稱。我這樣做是為了強調動態查詢中的變數和主腳本中的變數是不同的事實。是您在動態查詢中使用的變數@tmpvalue，而@somevalue是您的主要作用域的變數，您將作為儲存在@tmpvalue. 這是在動態查詢的變數和呼叫腳本的變數之間交換數據的方式。另請注意，您必須OUTPUT在參數聲明中和指定接收器變數時都指定。這與使用者定義的儲存過程的輸出參數的約定相同。

所以，總而言之，最終的程式碼看起來像這樣：

DECLARE
 @sql      nvarchar(max),
 @somvalue nvarchar(16)
;

SET @sql =
N'SELECT
 @tmpvalue = myresult
FROM
 OPENQUERY(
   test, 
   ''SELECT getvalue(' + CAST(@someargument AS nvarchar(16)) + N') AS myresult FROM DUAL''
 )
';

EXEC sp_executesql
 @sql,
 N'@tmpvalue nvarchar(16) OUTPUT',
 @somevalue OUTPUT
;

---

1實際上，您可以使用 EXEC 擁有參數，包括輸出參數，但前提是您已指定在遠端伺服器上執行動態查詢：

EXEC (@sql, @var1, @var2, ...) AT linked_server

通常，當遠端伺服器是非 SQL Server 時，這種方法更有用，因為對於 SQL Server 實例，這種EXEC sp_executesql方法可能更可取。

引用自：https://dba.stackexchange.com/questions/108929