wake-up-neo.com

Wählen Sie Spalten aus der Ergebnismenge der gespeicherten Prozedur aus

Ich habe eine gespeicherte Prozedur, die 80 Spalten und 300 Zeilen zurückgibt. Ich möchte eine Auswahl schreiben, die 2 dieser Spalten erhält. So etwas wie 

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

Wenn ich die obige Syntax verwendet habe, erhalte ich den Fehler: 

"Spaltenname ungültig".

Ich weiß, die einfachste Lösung wäre, die gespeicherte Prozedur zu ändern, aber ich habe sie nicht geschrieben und kann sie nicht ändern. 

Gibt es eine Möglichkeit zu tun, was ich will?

  • Ich könnte eine temporäre Tabelle erstellen, um die Ergebnisse einzufügen, aber da es 80 Spalten gibt, müsste ich eine temporäre Tabelle mit 80 Spalten erstellen, um nur 2 Spalten zu erhalten. Ich wollte es vermeiden, alle zurückgegebenen Spalten aufzuspüren.

  • Ich habe versucht, WITH SprocResults AS ...., wie von Mark vorgeschlagen, zu verwenden, bekam aber 2 Fehler 

    Falsche Syntax in der Nähe des Schlüsselworts 'EXEC'.
    Falsche Syntax in der Nähe ')'.

  • Ich habe versucht, eine Tabellenvariable zu deklarieren und erhielt den folgenden Fehler 

    Fehler beim Einfügen: Der Spaltenname oder die Anzahl der angegebenen Werte stimmt nicht mit der Tabellendefinition überein

  • Wenn ich es versuche 
    SELECT * FROM EXEC MyStoredProc 'param1', 'param2'
    Ich bekomme den Fehler:

    Falsche Syntax in der Nähe des Schlüsselworts "exec".

404
Rossini

Kannst du die Frage aufteilen? Fügen Sie die gespeicherten proc-Ergebnisse in eine Tabellenvariable oder eine temporäre Tabelle ein. Wählen Sie dann die 2 Spalten aus der Tabellenvariablen aus.

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

SELECT col1, col2 FROM @tablevar
168
Gulzar Nazim

Hier ist ein Link zu einem ziemlich guten Dokument, in dem die verschiedenen Möglichkeiten beschrieben werden, um Ihr Problem zu lösen (obwohl viele von ihnen nicht verwendet werden können, da Sie die vorhandene gespeicherte Prozedur nicht ändern können.)

So geben Sie Daten zwischen gespeicherten Prozeduren frei

Gulzars Antwort wird funktionieren (es ist im obigen Link dokumentiert), aber das Schreiben wird mühsam (Sie müssen alle 80 Spaltennamen in Ihrer @tablevar-Anweisung (col1, ...) angeben. In Zukunft Wenn dem Schema eine Spalte hinzugefügt oder die Ausgabe geändert wird, muss sie in Ihrem Code aktualisiert werden, oder es wird ein Fehler ausgegeben.

81
Lance McNearney
CREATE TABLE #Result
(
  ID int,  Name varchar(500), Revenue money
)
INSERT #Result EXEC RevenueByAdvertiser '1/1/10', '2/1/10'
SELECT * FROM #Result ORDER BY Name
DROP TABLE #Result

Quelle:
http://stevesmithblog.com/blog/select-from-a-stored-procedure/

75
Peter Nazarov

Dies funktioniert für mich: (d. H. Ich brauche nur 2 Spalten der 30+, die von sp_help_job zurückgegeben werden) 

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

Bevor dies funktionieren konnte, musste ich Folgendes ausführen:

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

.... um die sys.servers-Tabelle zu aktualisieren. (Die Verwendung einer Selbstreferenz in OPENQUERY scheint standardmäßig deaktiviert zu sein.)

Für meine einfache Anforderung bin ich auf keines der Probleme gestoßen, die im Abschnitt OPENQUERY von Lances hervorragender Verbindung beschrieben werden. 

Rossini, wenn Sie diese Eingabeparameter dynamisch einstellen müssen, wird die Verwendung von OPENQUERY etwas fummiger:

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);

Ich bin mir nicht sicher, ob es Unterschiede gibt (falls vorhanden) zwischen der Verwendung von sp_serveroption zum direkten Aktualisieren der vorhandenen sys.servers-Selbstreferenz und der Verwendung von sp_addlinkedserver (wie in Lances Link beschrieben), um ein Duplikat/Alias ​​zu erstellen.

Hinweis 1: Ich bevorzuge OPENQUERY gegenüber OPENROWSET, da für OPENQUERY keine Definition der Verbindungszeichenfolge in der Prozedur erforderlich ist. 

Anmerkung 2: Nachdem ich all das gesagt habe: Normalerweise würde ich einfach INSERT ... EXEC verwenden. :) Ja, es sind 10 Minuten extra Eingabe, aber wenn ich helfen kann, jogge ich lieber nicht mit:
(a) Anführungszeichen innerhalb von Anführungszeichen und
(b) sys-Tabellen und/oder hinterlistige, auf sich selbst verweisende Linked Server-Setups (d. h. für diese muss ich meinen Fall bei unseren leistungsstarken Datenbankadministratoren geltend machen :) 

In diesem Fall konnte ich jedoch kein INSERT ... EXEC-Konstrukt verwenden, da sp_help_job bereits eines verwendet. ("Eine INSERT EXEC-Anweisung kann nicht geschachtelt werden.")

37
Merenzo

Es kann hilfreich sein zu wissen, warum dies so schwierig ist. Eine gespeicherte Prozedur kann nur Text (print 'text') oder mehrere Tabellen oder überhaupt keine Tabellen zurückgeben.

So etwas wie SELECT * FROM (exec sp_tables) Table1 wird nicht funktionieren

10
newbie007

Wenn Sie Ihre gespeicherte Prozedur ändern können, können Sie die erforderlichen Spaltendefinitionen problemlos als Parameter eingeben und eine automatisch erstellte temporäre Tabelle verwenden:

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

In diesem Fall müssen Sie eine temporäre Tabelle nicht manuell erstellen - sie wird automatisch erstellt. Hoffe das hilft.

9
dyatchenko

Um dies zu erreichen, erstellen Sie zunächst einen #test_table wie folgt:

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

Führen Sie nun die Prozedur aus und geben Sie den Wert in #test_table ein:

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

Nun holen Sie den Wert von #test_table:

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

(SQL Server voraussetzen)

Die einzige Möglichkeit, mit den Ergebnissen einer gespeicherten Prozedur in T-SQL zu arbeiten, ist die Verwendung der INSERT INTO ... EXEC-Syntax. Dadurch haben Sie die Möglichkeit, in eine temporäre Tabelle oder eine Tabellenvariable einzufügen und dort die gewünschten Daten auszuwählen.

8
Brannon

Wenn Sie dies zur manuellen Validierung der Daten tun, können Sie dies mit LINQPad tun.

Erstellen Sie in LinqPad eine Verbindung zur Datenbank und erstellen Sie anschließend C # -Anweisungen, die den folgenden ähneln:

DataTable table = MyStoredProc (param1, param2).Tables[0];
(from row in table.AsEnumerable()
 select new
 {
  Col1 = row.Field<string>("col1"),
  Col2 = row.Field<string>("col2"),
 }).Dump();

Referenz http://www.global-webnet.net/blogengine/post/2008/09/10/LINQPAD-User-Stored-Procedures-Accessing-a-DataSet.aspx

7
ShawnFeatherly

Ein schneller Hack wäre, einen neuen Parameter '@Column_Name' hinzuzufügen und die aufrufende Funktion den abzurufenden Spaltennamen definieren zu lassen. Im Rückgabeabschnitt Ihres Sproc hätten Sie if/else -Anweisungen und geben nur die angegebene Spalte zurück.

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
Samir Basic

Für SQL Server finde ich, dass dies gut funktioniert:

Erstellen Sie eine temporäre Tabelle (oder eine permanente Tabelle, spielt keine Rolle) und fügen Sie eine Anweisung in die gespeicherte Prozedur ein. Die Ergebnismenge von SP sollte mit den Spalten in Ihrer Tabelle übereinstimmen. Andernfalls wird ein Fehler angezeigt.

Hier ist ein Beispiel:

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;

Das ist es!

6
LTMOD

versuche dies

use mydatabase
create procedure sp_onetwothree as
select 1 as '1', 2 as '2', 3 as '3'
go
SELECT a.[1], a.[2]
FROM OPENROWSET('SQLOLEDB','myserver';'sa';'mysapass',
    'exec mydatabase.dbo.sp_onetwothree') AS a
GO
4
SelvirK

Wie in der Frage erwähnt, ist es schwierig, die Temp-Tabelle mit 80 Spalten zu definieren, bevor die gespeicherte Prozedur ausgeführt wird.

Die andere Möglichkeit besteht darin, die Tabelle basierend auf der Ergebnismenge der gespeicherten Prozedur aufzufüllen.

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

Wenn Sie eine Fehlermeldung erhalten, müssen Sie Ad-hoc-Abfragen aktivieren, indem Sie die folgende Abfrage ausführen.

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

Um sp_configure mit beiden Parametern auszuführen, um eine Konfigurationsoption zu ändern oder die RECONFIGURE-Anweisung auszuführen, müssen Sie die Servercode-Berechtigung ALTER SETTINGS erteilen

Jetzt können Sie Ihre spezifischen Spalten aus der generierten Tabelle auswählen

SELECT col1, col2
FROM #temp
4
sqluser

Ich weiß, dass das Ausführen von sp und Einfügen in temporäre Tabellen oder Tabellenvariablen eine Option wäre, aber ich denke nicht, dass dies Ihre Anforderung ist. Gemäß Ihrer Anforderung sollte die folgende Abfrageanweisung funktionieren:

Declare @sql nvarchar(max)
Set @sql='SELECT   col1, col2 FROM OPENROWSET(''SQLNCLI'', ''Server=(local);uid=test;pwd=test'',
     ''EXEC MyStoredProc ''''param1'''', ''''param2'''''')'
 Exec(@sql)

wenn Sie eine vertrauenswürdige Verbindung haben, verwenden Sie die folgende Abfrage:

Declare @sql nvarchar(max)
Set @sql='SELECT   col1, col2 FROM OPENROWSET(''SQLNCLI'', ''Server=(local);Trusted_Connection=yes;'',
     ''EXEC MyStoredProc ''''param1'''', ''''param2'''''')'
 Exec(@sql)

wenn beim Ausführen der obigen Anweisung ein Fehler auftritt, führen Sie einfach die folgende Anweisung aus:

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

Ich hoffe, dass dies jemandem hilft, der sich mit einem ähnlichen Problem konfrontiert sieht. Wenn jemand versuchen möchte, eine temporäre Tabelle oder eine Tabellenvariable zu verwenden, die wie folgt aussehen sollte, Sie aber in diesem Szenario wissen müssen, wie viele Spalten Ihr SP zurückgibt, sollten Sie so viele Spalten in einer temporären Tabelle oder einer Tabellenvariablen erstellen:

--for table variable 
Declare @t table(col1 col1Type, col2 col2Type)
insert into @t exec MyStoredProc 'param1', 'param2'
SELECT col1, col2 FROM @t

--for temp table
create table #t(col1 col1Type, col2 col2Type)
insert into #t exec MyStoredProc 'param1', 'param2'
SELECT col1, col2 FROM #t
1
Humayoun_Kabir

Für jeden, der über SQL 2012 oder später verfügt, konnte ich dies mit gespeicherten Prozeduren erreichen, die nicht dynamisch sind und jeweils die gleichen Spalten ausgeben.

Die allgemeine Idee ist, dass ich die dynamische Abfrage zum Erstellen, Einfügen, Auswählen und Löschen der temporären Tabelle erstellt und diese ausführt, nachdem sie generiert wurde. Ich generiere die temporäre Tabelle dynamisch, indem ich zuerst Spaltennamen und -typen aus der gespeicherten Prozedur abrufe .

Hinweis: Es gibt viel bessere, universellere Lösungen, die mit weniger Codezeilen funktionieren, wenn Sie die SP aktualisieren oder die Konfiguration ändern und OPENROWSET verwenden möchten. Verwenden Sie das Folgende, wenn Sie keine andere Möglichkeit haben.

DECLARE @spName VARCHAR(MAX) = 'MyStoredProc'
DECLARE @tempTableName VARCHAR(MAX) = '#tempTable'

-- might need to update this if your param value is a string and you need to escape quotes
DECLARE @insertCommand VARCHAR(MAX) = 'INSERT INTO ' + @tempTableName + ' EXEC MyStoredProc @param=value'

DECLARE @createTableCommand VARCHAR(MAX)

-- update this to select the columns you want
DECLARE @selectCommand VARCHAR(MAX) = 'SELECT col1, col2 FROM ' + @tempTableName

DECLARE @dropCommand VARCHAR(MAX) = 'DROP TABLE ' + @tempTableName

-- Generate command to create temp table
SELECT @createTableCommand = 'CREATE TABLE ' + @tempTableName + ' (' +
    STUFF
    (
        (
            SELECT ', ' + CONCAT('[', name, ']', ' ', system_type_name)
            FROM sys.dm_exec_describe_first_result_set_for_object
            (
              OBJECT_ID(@spName), 
              NULL
            )
            FOR XML PATH('')
        )
        ,1
        ,1
        ,''
    ) + ')'

EXEC( @createTableCommand + ' '+ @insertCommand + ' ' + @selectCommand + ' ' + @dropCommand)
0
Emil

Einfachste Methode, wenn Sie dies nur einmal benötigen:

Im Import- und Export-Assistenten nach Excel exportieren und dann dieses Excel in eine Tabelle importieren.

0
Martijn Tromp