Spalten aus der Ergebnismenge einer gespeicherten Prozedur auswählen

Question

Ich habe eine gespeicherte Prozedur, die 80 Spalten und 300 Zeilen zurückgibt. Ich möchte einen Select schreiben, der 2 dieser Spalten abruft. Etwas wie

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

Wenn ich die obige Syntax verwende, erhalte ich den Fehler:

"Ungültiger Spaltenname".

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, das zu tun, was ich möchte?

• Ich könnte eine temporäre Tabelle erstellen, um die Ergebnisse darin zu speichern, 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 vermeiden, dass alle Spalten, die zurückgegeben werden, nachverfolgt werden.

• Ich habe es mit WITH SprocResults AS .... wie von Mark vorgeschlagen, aber ich bekam 2 Fehler

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

• Ich habe versucht, eine Tabellenvariable zu deklarieren und erhielt folgende Fehlermeldung

  Fehler einfügen: Spaltenname oder Anzahl der gelieferten Werte stimmt nicht mit der Tabellendefinition überein

• Wenn ich versuche
  SELECT * FROM EXEC MyStoredProc 'param1', 'param2'
  Ich erhalte die Fehlermeldung :

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

Answer 1

Können Sie die Abfrage aufteilen? Fügen Sie die Ergebnisse der gespeicherten Abfrage 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

Answer 2

Hier ist ein Link zu einem recht guten Dokument, in dem alle verschiedenen Möglichkeiten zur Lösung Ihres Problems erläutert werden (obwohl viele davon nicht verwendet werden können, da Sie die vorhandene gespeicherte Prozedur nicht ändern können).

Gemeinsame Nutzung von Daten zwischen gespeicherten Prozeduren

Die Antwort von Gulzar wird funktionieren (sie ist im obigen Link dokumentiert), aber es wird mühsam sein, sie zu schreiben (Sie müssen alle 80 Spaltennamen in Ihrer @tablevar(col1,...) Anweisung angeben. Und in der Zukunft, wenn eine Spalte zum Schema hinzugefügt oder die Ausgabe geändert wird, muss sie in Ihrem Code aktualisiert werden, sonst tritt ein Fehler auf.

Answer 3

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/

Answer 4

Das funktioniert für mich: (d.h. ich brauche nur 2 Spalten von den 30+, die von sp_help_job )

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

Bevor das funktionieren würde, musste ich dies ausführen:

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

....zur Aktualisierung der sys.servers Tabelle. (d.h. die Verwendung einer Selbstreferenz innerhalb von OPENQUERY scheint standardmäßig deaktiviert zu sein).

Für meine einfache Anforderung hatte ich keines der Probleme, die in der OPENQUERY-Abschnitt von Lance's ausgezeichnetem Link.

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

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 = '''+@param1+''', @job_aspect = N'''+@param2+'''' ;

-- 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 zwischen der Verwendung von sp_serveroption zur Aktualisierung der bestehenden sys.servers Selbstreferenz direkt, im Gegensatz zur Verwendung von sp_addlinkedserver (wie in Lance's Link beschrieben), um ein Duplikat/Alias zu erstellen.

Anmerkung 1: Ich ziehe OPENQUERY gegenüber OPENROWSET vor, da OPENQUERY keine Definition des Verbindungsstrings innerhalb des Proc erfordert.

Anmerkung 2: Nach alledem: Normalerweise würde ich nur INSERT ... EXEC verwenden :) Ja, es sind 10 Minuten zusätzliche Tipparbeit, aber wenn ich es vermeiden kann, ziehe ich es vor, nicht damit herumzuspielen:
(a) Anführungszeichen in Anführungszeichen in Anführungszeichen und
(b) Sys-Tabellen und/oder raffinierte selbstreferenzierende Linked-Server-Konfigurationen (d. h. für diese muss ich mich an unsere allmächtigen DBAs wenden :)

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

Answer 5

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

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