404


75

ストアドプロシージャの結果セットから列を選択する

私は80列と300行を返すストアドプロシージャを持っています。 これらの列を2つ取得するselectを書きたいと思います。 何かのようなもの

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

上記の構文を使用した場合、エラーが発生します。

_ 「無効な列名」。 _

私は最も簡単な解決策はストアドプロシージャを変更することであることを知っています、しかし私はそれを書きませんでした、そして私はそれを変更することができません。

私がやりたいことをする方法はありますか?

  • 結果を入れる一時テーブルを作成できましたが、 80列なので、2列を取得するために80列の一時テーブルを作成する必要があります。 返されるすべての列を追跡しないようにしたいです。

  • Markが示唆するように「WITH SprocResults AS …​.」を使用してみましたが、 2つのエラーが発生しました+
    _ キーワード「EXEC」の近くの誤った構文。 ')'付近の構文が正しくありません。 _

  • テーブル変数を宣言しようとすると、次のエラーが発生しました+

    _ 挿入エラー:列名または指定された値の数がテーブル定義と一致しません _

  • 私が試してみると SELECT * FROM EXEC MyStoredProc 'param1'、 'param2' エラーが表示されます:
    _ キーワード「exec」付近の構文が正しくありません。 _

16 回答


168


クエリを分割できますか? ストアドプロシージャの結果をテーブル変数または一時テーブルに挿入します。 次に、テーブル変数から2つの列を選択します。

@tablevarテーブルを宣言します(col1 col1Type、.. @tablevar(col1、..)exec MyStoredProc 'param1'、 'param2'に挿入します。

SELECT col1、col2 FROM @tablevar


81


ここにあなたの問題を解決するためのすべての異なる方法を説明するかなり良い文書へのリンクがあります(あなたが既存のストアドプロシージャを修正することができないのでそれらの多くは使用できませんが)。

http://www.sommarskog.se/share_data.html[ストアドプロシージャ間でデータを共有する方法]

Gulzarの答えは上手くいきますが(上のリンクに文書化されています)、書くのは面倒です(@tablevar(col1、…​)ステートメントに80個すべての列名を指定する必要があります)。 そして将来的には、列がスキーマに追加されたり出力が変更されたりした場合、それはあなたのコード内で更新される必要があり、そうでなければエラーとなるでしょう。


75


CREATE TABLE#結果(ID int、Name varchar(500)、収入)INSERT#結果EXEC RevenueByAdvertiser '1/1/10'、 '2/1/10' SELECT * FROM#結果ORDER BY名前DROP TABLE #Result

ソース:http://stevesmithblog.com/blog/select-from-a-stored-procedure/


37


これは私のために働く:(すなわち 私は `sp_help_job`によって返される30のうち2つの列だけを必要とします。

SELECT name, current_execution_status
FROM OPENQUERY (MYSERVER,
  'EXEC msdb.dbo.sp_help_job @job_name = ''My Job'', @job_aspect = ''JOB''');

これが機能する前に、私はこれを実行する必要がありました:

sp_serveroption 'MYSERVER', 'DATA ACCESS', TRUE;

…​.to update the sys.servers table. (i.e. Using a self-reference OPENQUERY内はデフォルトで無効になっているようです。)

私の単純な要件のために、私はLanceのすばらしいリンクのhttp://www.sommarskog.se/share_data.html#OPENQUERY [OPENQUERYセクション]に記述されている問題のどれにも出くわしませんでした。

Rossini、これらの入力パラメータを動的に設定する必要がある場合は、OPENQUERYの使用がもう少し手間がかかります。

DECLARE @innerSql varchar(1000);
DECLARE @outerSql varchar(1000);

-- Set up the original stored proc definition.
SET @innerSql =
'EXEC msdb.dbo.sp_help_job @job_name = '''[email protected]+''', @job_aspect = N'''[email protected]+'''' ;

-- Handle quotes.
SET @innerSql = REPLACE(@innerSql, '''', '''''');

-- Set up the OPENQUERY definition.
SET @outerSql =
'SELECT name, current_execution_status
FROM OPENQUERY (MYSERVER, ''' + @innerSql + ''');';

-- Execute.
EXEC (@outerSql);

既存の sys.servers`自己参照を直接更新するために sp_serveroption`を使うことの違いがあるとしても、私は違いがわかりません。 (sp_addlinkedserver)を使って(Lanceのリンクで説明されているように)複製/別名を作成します。

注意1:OPENQUERYはproc内で接続文字列の定義を必要としないため、OPENROWSETよりもOPENQUERYの方が好きです。

注2:これをすべて言ったことで、通常はINSERTを使用します。 EXEC :) Yes, it’s 10 mins extra typing, but if I can help it, I prefer not to (a)引用符内の引用符内の引用符、および/または(b)sysテーブル、および/または卑劣な自己参照型リンクサーバー設定(またはその両方) これらのために、私は私達のすべての強力なDBAに私の主張を訴える必要があります:)

しかし、この例では、私はINSERTを使用することができませんでした…​ `sp_help_job`はすでに使っているので、EXEC構文。 ( "INSERT EXECステートメントはネストできません。")


10


これがなぜそれほど難しいのかを知ることは役に立つかもしれません。 ストアドプロシージャはテキスト(print 'text')のみを返すか、複数のテーブルを返すか、またはテーブルをまったく返さないことがあります。

そのため、「SELECT * FROM(exec sp_tables)Table1」のようなものは機能しません。


9


これを達成するために、まず以下のような `#test_table`を作成します。

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

今度はprocedureを実行し、 `#test_table`に値を入れます。

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

今度は `#test_table`から値を取得します。

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


9


ストアドプロシージャを変更できる場合は、必要な列定義をパラメータとして簡単に配置して、自動作成された一時テーブルを使用できます。

CREATE PROCEDURE sp_GetDiffDataExample @columnsStatement NVARCHAR(MAX) - 必要な列ステートメント(例: "field1、field2")開始宣言@query NVARCHAR(MAX)SET @query = N'SELECT '@columnsStatement N' INTO ## TempTable FROM dbo.TestTable 'EXEC sp_executeSql @query SELECT * FROM ## TempTable DROP TABLE ##一時テーブルEND

この場合、一時テーブルを手動で作成する必要はありません。一時テーブルは自動的に作成されます。 お役に立てれば。


8


(SQL Serverを想定)

T-SQLでストアドプロシージャの結果を処理する唯一の方法は、 `INSERT INTO …​を使用することです。 EXECの構文 これにより、一時テーブルまたはテーブル変数に挿入し、そこから必要なデータを選択することができます。


7


手っ取り早い方法は、新しいパラメータ `` @ Column_Name'`を追加し、呼び出し側の関数に取得する列名を定義させることです。 sprocのreturn部分には、if / else文を指定して指定した列のみを返すか、空の場合はallを返します。

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


7


データの手動検証のためにこれをしているのなら、LINQPadを使ってこれを行うことができます。

LinqPadでデータベースへの接続を作成してから、次のようなC#ステートメントを作成します。

DataTableテーブル= MyStoredProc(param1、param2).Tables [0]; (table.AsEnumerable()の行からnew {Col1 = row.Field( "col1")、Col2 = row.Field( "col2")、})を選択します。

参照http://www.global-webnet.net/blogengine/post/2008/09/10/LINQPAD-Using-Stored-Procedures-Accessing-a.co.jpSet.aspx