sql-server - query - test stored procedure in sql server




從存儲過程的結果集中選擇列 (11)

(假設SQL Server)

在T-SQL中使用存儲過程的結果的唯一方法是使用INSERT INTO ... EXEC語法。 這使您可以選擇插入臨時表或表變量,並從那裡選擇所需的數據。

我有一個存儲過程返回80列和300行。 我想寫一個選擇獲取那些列中的2個。 就像是

SELECT col1, col2 FROM EXEC MyStoredProc 'param1', 'param2'

當我使用上面的語法時,我得到錯誤:

“無效的列名稱”。

我知道最簡單的解決方案是更改存儲過程,但我沒有寫入,而且我也無法更改它。

有什麼辦法可以做我想做的事嗎?

  • 我可以創建一個臨時表來放置結果,但是因為有80列,所以我需要創建一個80列臨時表才能獲得2列。 我想避免追踪所有返回的列。

  • 我嘗試使用WITH SprocResults AS ....按照Mark的建議,但我得到了2個錯誤

    關鍵字'EXEC'附近的語法不正確。
    ')'附近語法不正確。

  • 我試圖聲明一個表變量,我得到以下錯誤

    插入錯誤:提供的值的列名或數量與表定義不匹配

  • 如果我嘗試
    SELECT * FROM EXEC MyStoredProc 'param1', 'param2'
    我收到錯誤:

    關鍵字'exec'附近的語法不正確。


下面是一個很好的文檔鏈接,解釋了解決問題的所有不同方法(儘管由於無法修改現有存儲過程,因此很多方法都無法使用)。

如何在存儲過程之間共享數據

Gulzar的答案會起作用(它在上面的鏈接中有記錄),但寫起來很麻煩(您需要在@tablevar(col1,...)語句中指定所有80個列名稱。如果一個列被添加到模式或者輸出被改變了,它將需要在你的代碼中被更新,否則它將會出錯。


你有沒有試過這個:

  • 創建一個應該調用這個StoredProcedure的函數
  • 使用游標來存儲從StoredProcedure返回的結果
  • 循環該游標,以便您可以將需要的兩列打印/存儲到臨時表(僅限2列)
  • 然後使用該表選擇結果

你能分解查詢嗎? 將存儲的proc結果插​​入表變量或臨時表中。 然後,從表變量中選擇2列。

Declare @tablevar table(col1 col1Type,..
insert into @tablevar(col1,..) exec MyStoredProc 'param1', 'param2'

SELECT col1, col2 FROM @tablevar

如果你只需要這樣一次,最簡單的方法就是:

在導入和導出嚮導中導出為excel,然後將此excel導入到表中。


如果您可以修改存儲過程,則可以輕鬆將所需的列定義作為參數並使用自動創建的臨時表:

CREATE PROCEDURE sp_GetDiffDataExample
      @columnsStatement NVARCHAR(MAX) -- required columns statement (e.g. "field1, field2")
AS
BEGIN
    DECLARE @query NVARCHAR(MAX)
    SET @query = N'SELECT ' + @columnsStatement + N' INTO ##TempTable FROM dbo.TestTable'
    EXEC sp_executeSql @query
    SELECT * FROM ##TempTable
    DROP TABLE ##TempTable
END

在這種情況下,您不需要手動創建臨時表 - 它會自動創建。 希望這可以幫助。


對於SQL Server,我發現這工作正常:

創建臨時表(或永久表,實際上並不重要),並對存儲過程執行插入語句。 SP的結果集應與表中的列匹配,否則會出現錯誤。

這是一個例子:

DECLARE @temp TABLE (firstname NVARCHAR(30), lastname nvarchar(50));

INSERT INTO @temp EXEC dbo.GetPersonName @param1,@param2;
-- assumption is that dbo.GetPersonName returns a table with firstname / lastname columns

SELECT * FROM @temp;

而已!


快速'@Column_Name'將是添加一個新的參數'@Column_Name'並讓調用函數定義要檢索的列名。 在你的sproc的返回部分,你會有if / else語句並且只返回指定的列,或者如果是空的 - 返回全部。

CREATE PROCEDURE [dbo].[MySproc]
        @Column_Name AS VARCHAR(50)
AS
BEGIN
    IF (@Column_Name = 'ColumnName1')
        BEGIN
            SELECT @ColumnItem1 as 'ColumnName1'
        END
    ELSE
        BEGIN
            SELECT @ColumnItem1 as 'ColumnName1', @ColumnItem2 as 'ColumnName2', @ColumnItem3 as 'ColumnName3'
        END
END

正如問題中提到的那樣,在執行存儲過程之前很難定義80列臨時表。

因此,另一種方法是根據存儲過程結果集來填充表。

SELECT * INTO #temp FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;'
                                   ,'EXEC MyStoredProc')

如果您遇到任何錯誤,您需要通過執行以下查詢來啟用臨時分佈式查詢。

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

要使用這兩個參數執行sp_configure來更改配置選項或運行RECONFIGURE語句,您必須被授予ALTER SETTINGS服務器級權限

現在您可以從生成的表格中選擇您的特定列

SELECT col1, col2
FROM #temp

為了達到這個目的,首先創建一個#test_table如下所示:

create table #test_table(
    col1 int,
    col2 int,
   .
   .
   .
    col80 int
)

現在執行程序並將值放入#test_table

insert into #test_table
EXEC MyStoredProc 'param1', 'param2'

現在您從#test_table獲取值:

select col1,col2....,col80 from #test_table





stored-procedures