如何使用OPENROWSET執行帶引數的存盤程序并將結果插入臨時表-有解無憂

我想使用OPENROWSET. 但是，我遇到的問題是我無法將引數傳遞給我的存盤程序。

這是我的存盤程序：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[N_spRetrieveStatement]
    @PeopleCodeId nvarchar(10),
    @StatementNumber int
AS
    SET NOCOUNT ON

    DECLARE @PersonId int
    SELECT @PersonId = [dbo].[fnGetPersonId](@PeopleCodeId)

    SELECT * 
    INTO #tempSpRetrieveStatement 
    FROM OPENROWSET('SQLNCLI', 'Server=PCPRODDB01;Trusted_Connection=yes;',
                    'EXEC Campus.dbo.spRetrieveStatement @StatementNumber, @PersonId');
     
    --2577, 15084

    SELECT * 
    FROM #tempSpRetrieveStatement;

uj5u.com熱心網友回復：

OpenRowSet 不允許您使用輸入引數執行程序。您必須使用 INSERT/EXEC。

INTO #tempSpRetrieveStatement(Col1, Col2,...)
EXEC PCPRODDB01.Campus.dbo.spRetrieveStatement @StatementNumber, @PersonId

在運行上述命令之前，為 PCPRODDB01 創建和測驗 LinkedServer。

uj5u.com熱心網友回復：

鑒于您提供的代碼示例，您的問題的根源是您的陳述句中實際上沒有引數，您正在傳輸到您正在連接的遠程服務器。即使它是您連接的同一臺機器，它們也會處于不同的行程中，并且另一個行程無權訪問您的會話變數。

提到 LinkedServer 作為一個選項，我的理解是這是首選選項。然而在實踐中，由于技術或組織限制的本地怪癖，這并不總是可用的。它發生了。

但是有一種方法可以做到這一點。

它隱藏在眾目睽睽之下。

您需要將文字傳遞到將在另一臺服務器上執行的字串中，對嗎？

因此，您首先構建將執行此操作的字串。

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[N_spRetrieveStatement]
    @PeopleCodeId nvarchar(10),
    @StatementNumber int
AS

SET NOCOUNT ON

DECLARE
    @PersonId INT,
    @TempSQL VARCHAR(4000) = '';

SELECT @PersonId = [dbo].[fnGetPersonId](@PeopleCodeId);


SET @TempSQL =
    'EXEC Campus.dbo.spRetrieveStatement('''''   
        FORMAT(@StatementNumber,'D')  ''''', '''''  
        FORMAT(@PersonId,'D')   ''''')';
        --2577, 15084

請注意看似過多的引號。這不是一個錯誤——這是一個預兆。因為，是的，OPENROWSET 討厭將變數作為引數。它也只需要文字。那么，我們如何給 OPENROWSET 它所需要的東西呢？

我們創建一個字串，它是整個陳述句，沒有任何型別的變數。我們執行它。

SET @TempSQL = 
    'SELECT * INTO #tempSpRetrieveStatement '  
    'FROM OPENROWSET(''SQLNCLI'', ''Server=PCPRODDB01;Trusted_Connection=yes;'', '   @TempSQL   
                    'EXEC Campus.dbo.spRetrieveStatement @StatementNumber, @PersonId';

EXEC (@TempSQL);

SELECT * 
FROM #tempSpRetrieveStatement;

就是這樣！很簡單，除了計算你的轉義引號，對吧？

現在......這幾乎超出了您提出的問題的范圍，但這是我在通過 OPENROWSET 在另一臺機器上執行存盤程序時遇到的一個“問題”。您顯然習慣于使用臨時表。如果您正在呼叫的存盤程序正在創建臨時表或執行其他一些事情，這將失敗 - 簡而言之 - 會在 SQL 服務器中引發模棱兩可的恐懼。它不喜歡模棱兩可。如果是這種情況，您將看到如下訊息：

“訊息 11514，級別 16，狀態 1，程序 sp_describe_first_result_set，第 1 行

無法確定元資料，因為陳述句“……您的遠程 EXEC 陳述句……”在程序“……您的本地存盤程序的名稱……”中包含動態 SQL。考慮使用 WITH RESULT SETS 子句來明確描述結果集。”

那么，這是怎么回事？

您不僅可以使用 OPENROWSET 取回資料。本地服務器和遠程服務器就本地服務器對遠程服務器的期望進行了簡短的交談（因此它可以優化接收和處理它——這對于大型行集來說非常重要）。從 SQL Server 2012 開始，sp_describe_first_result_set 是較新的程序，通常它會在您不注意的情況下快速執行。只是它的占卜之力不是無限的。也就是說，它不知道如何獲取有關臨時表的型別和名稱資訊（可能還有其他一些它不能做的事情——select 陳述句中的 PIVOT 可能是正確的）。

我特別想確保指出這一點，因為您回復了您對使用 LinkedServer 的猶豫。事實上，您猶豫不決的相同原因可能會使該錯誤訊息的建議完全無用——您甚至無法預測您將獲得哪些列以及以何種順序獲得它們。

我認為如果您只是根據條件陳述句向上游分支并且正在執行幾個潛在的 SELECT 陳述句之一，那么您正在做的事情將會起作用。我認為如果您只是不確定是否可以依賴被修復的上游組件并試圖確保即使它發生變化，這個程序也不必這樣做，因為它非常通用。

但另一方面，您面臨的情況是，您實際上無法保證 SQL Server 可以預測列，您可能不得不強制對您呼叫的存盤程序進行一些更改，以確保它是穩定的。例如，您可以通過使用 CASE 運算式而不是任何 PIVOT 來確定如何確保所有可能的欄位始終存在。您可能會創建一個會話表，專門用于容納您需要的 SELECT 足夠長的時間來執行此操作，然后將內容從那里洗掉。您可能會更改傳輸資料的方式，使其基本上通過等效的UNPIVOT 的。在所有這些額外的作業之后，如果您使用 LinkedServer 或 OPENROWSET 來移植資料，也許這只是一個偏好問題。

這就是您提出的字面問題的答案，也是您可以對答案做些什么的限制之一。

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/410818.html

標籤：

上一篇：檢查SQLServer的謂詞篩選器中是否存在表

下一篇：使用主鍵約束撰寫的最佳實踐？