Kolommen selecteren uit de resultatenset van de opgeslagen procedure

Ik heb een opgeslagen procedure die 80 kolommen en 300 rijen retourneert. Ik wil een select schrijven die 2 van die kolommen krijgt. Iets als

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

Toen ik de bovenstaande syntaxis gebruikte, kreeg ik de foutmelding:

“Ongeldige kolomnaam”.

Ik weet dat de eenvoudigste oplossing zou zijn om de opgeslagen procedure te wijzigen, maar ik heb het niet geschreven en ik kan het niet veranderen.

Is er een manier om te doen wat ik wil?

• Ik zou een tijdelijke tabel kunnen maken om de resultaten in te plaatsen, maar omdat er 80 kolommen zijn, zou ik een tijdelijke tabel met 80 kolommen moeten maken om 2 kolommen te krijgen. Ik wilde voorkomen dat alle kolommen worden opgespoord die worden geretourneerd.

• Ik heb geprobeerd WITH SprocResults AS ....te gebruiken zoals gesuggereerd door Mark, maar ik kreeg 2 fouten

  Onjuiste syntaxis bij het zoekwoord ‘EXEC’.
  Onjuiste syntaxis bij ‘)’.

• Ik heb geprobeerd een tabelvariabele te declareren en kreeg de volgende foutmelding

  Invoegfout: kolomnaam of aantal opgegeven waarden komt niet overeen met tabeldefinitie

• Als ik probeer
  SELECT * FROM EXEC MyStoredProc 'param1', 'param2'
  Ik krijg de foutmelding:

  Onjuiste syntaxis bij het zoekwoord ‘exec’.

Antwoord 1, autoriteit 100%

Kun je de zoekopdracht opsplitsen? Plaats de opgeslagen proc-resultaten in een tabelvariabele of een tijdelijke tabel. Selecteer vervolgens de 2 kolommen van de tabelvariabele.

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

Antwoord 2, autoriteit 44%

Hier is een link naar een redelijk goed document waarin alle verschillende manieren worden uitgelegd om uw probleem op te lossen (hoewel veel ervan niet kunnen worden gebruikt omdat u de bestaande opgeslagen procedure niet kunt wijzigen.)

Gegevens delen tussen opgeslagen procedures

Het antwoord van Gulzar zal werken (het is gedocumenteerd in de link hierboven) maar het zal een hele klus zijn om te schrijven (je moet alle 80 kolomnamen specificeren in je @tablevar(col1,…) statement). als in de toekomst een kolom aan het schema wordt toegevoegd of de uitvoer wordt gewijzigd, moet deze in uw code worden bijgewerkt, anders treedt er een fout op.

Antwoord 3, autoriteit 40%

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

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

Antwoord 4, autoriteit 20%

Dit werkt voor mij: (d.w.z. ik heb slechts 2 kolommen van de 30+ nodig die worden geretourneerd door sp_help_job)

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

Voordat dit zou werken, moest ik dit uitvoeren:

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

….om de tabel sys.serversbij te werken. (d.w.z. het gebruik van een zelfreferentie binnen OPENQUERY lijkt standaard uitgeschakeld te zijn.)

Voor mijn eenvoudige behoefte kwam ik geen van de problemen tegen die worden beschreven in de OPENQUERY-sectievan Lance’s uitstekende link.

Rossini, als je die invoerparameters dynamisch moet instellen, wordt het gebruik van OPENQUERY wat lastiger:

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

Ik ben niet zeker van de verschillen (indien aanwezig) tussen het gebruik van sp_serveroptionom de bestaande sys.serverszelfreferentie rechtstreeks bij te werken, versus het gebruik van sp_addlinkedserver(zoals beschreven in de link van Lance) om een ​​duplicaat/alias te maken.

Opmerking 1:
Ik geef de voorkeur aan OPENQUERY boven OPENROWSET, aangezien OPENQUERY de definitie van de verbindingsreeks binnen het proces niet vereist.

Opmerking 2:
Dit alles gezegd hebbende: normaal gesproken zou ik gewoon INSERT … EXEC gebruiken 🙂 Ja, het is 10 minuten extra typen, maar als ik het kan helpen, ga ik liever niet rommelen met:
(a) aanhalingstekens binnen aanhalingstekens binnen aanhalingstekens, en
(b) sys-tabellen en/of stiekeme naar zichzelf verwijzende Linked Server-setups (d.w.z. hiervoor moet ik mijn zaak bepleiten bij onze almachtige DBA’s 🙂

In dit geval kon ik echter geen INSERT … EXEC-constructie gebruiken, omdat sp_help_jober al een gebruikt. (“Een INSERT EXEC-instructie kan niet worden genest.”)

Antwoord 5, autoriteit 5%

Om dit te bereiken, maakt u eerst een #test_tablezoals hieronder:

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

Voer nu de procedure uit en plaats de waarde in #test_table:

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

Je haalt nu de waarde op uit #test_table:

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

Antwoord 6, autoriteit 5%

Als u uw opgeslagen procedure kunt wijzigen, kunt u eenvoudig de vereiste kolomdefinities als parameter plaatsen en een automatisch gemaakte tijdelijke tabel gebruiken:

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 dit geval hoeft u geen tijdelijke tabel handmatig te maken – deze wordt automatisch gemaakt. Ik hoop dat dit helpt.

Antwoord 7, autoriteit 5%

Het is misschien handig om te weten waarom dit zo moeilijk is.
Een opgeslagen procedure kan alleen tekst retourneren (print ‘tekst’), of meerdere tabellen retourneren, of helemaal geen tabellen retourneren.

Dus zoiets als SELECT * FROM (exec sp_tables) Table1werkt niet

Antwoord 8, autoriteit 4%

(Ervan uitgaande dat SQL Server)

De enige manier om met de resultaten van een opgeslagen procedure in T-SQL te werken, is door de syntaxis INSERT INTO ... EXECte gebruiken. Dat geeft je de mogelijkheid om in een tijdelijke tabel of een tabelvariabele in te voegen en van daaruit de gegevens te selecteren die je nodig hebt.

Antwoord 9, autoriteit 4%

Een snelle hack zou zijn om een ​​nieuwe parameter '@Column_Name'toe te voegen en de aanroepende functie de kolomnaam te laten definiëren die moet worden opgehaald. In het return-gedeelte van je sproc zou je if/else statements hebben en alleen de gespecificeerde kolom retourneren, of als deze leeg is – return 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

10

Makkelijkste manier om te doen als u dit slechts één keer nodig hebt:

Exporteren naar Excel in Import and Export Wizard en importeer deze Excel vervolgens in een tabel.

11

Voor iedereen die SQL 2012 of later heeft, kon ik dit bereiken met opgeslagen procedures die niet dynamisch zijn en dezelfde kolommen per keer hebben.

Het algemene idee is dat ik de dynamische query build om te maken, in te voegen in, selecteert u de Temp-tabel en voert u dit uit nadat het allemaal gegenereerd is. Ik genereer de Temp-tabel dynamisch door de eerste kolomnamen en typen op te halen uit de opgeslagen procedure .

Opmerking: er zijn veel betere, universelere oplossingen die werken met minder regels code als je bereid/in staat bent om de SP te updaten of de configuratie te wijzigen en OPENROWSETte gebruiken. Gebruik het onderstaande als het niet anders kan.

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)

Antwoord 12

Ik zou de originele SP knippen en plakken en alle kolommen verwijderen behalve de 2 die je wilt. Of. Ik zou de resultaatset terugbrengen, het toewijzen aan een goed zakelijk object en vervolgens de twee kolommen LINQ eruit halen.