Wybierz kolumny z zestawu wyników procedury składowanej

Mam procedurę składowaną, która zwraca 80 kolumn i 300 wierszy. Chcę napisać zaznaczenie, które otrzyma 2 z tych kolumn. Coś jak

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

Gdy użyłem powyższej składni, pojawia się błąd:

"Nieprawidłowa nazwa kolumny".

Wiem, że najłatwiejszym rozwiązaniem byłoby zmienić procedurę przechowywaną, ale jej nie napisałem i nie mogę jej zmienić.

Czy jest jakiś sposób na robienie tego, co chcę?

• Mógłbym utworzyć tabelę tymczasową, aby umieścić wyniki, ale ponieważ istnieje 80 kolumn, musiałbym stworzyć 80-tabelową tabelę tymczasową, aby uzyskać 2 kolumny. Chciałem uniknąć śledzenia wszystkich zwracanych kolumn.

• Próbowałem używać WITH SprocResults AS ....zgodnie z sugestią Marka, ale otrzymałem 2 błędy
  

  Niepoprawna składnia w pobliżu słowa kluczowego „EXEC”.
  Niepoprawna składnia w pobliżu ')'.

• Próbowałem zadeklarować zmienną tabelową i otrzymałem następujący błąd
  

  Wstaw błąd: nazwa kolumny lub liczba podanych wartości nie zgadza się z definicją tabeli

• Jeśli spróbuję
  SELECT * FROM EXEC MyStoredProc 'param1', 'param2'
  , pojawia się błąd:
  

  Niepoprawna składnia w pobliżu słowa kluczowego „exec”.

Czy możesz podzielić zapytanie? Wstaw zapisane wyniki proc do zmiennej tabeli lub tabeli tymczasowej. Następnie wybierz 2 kolumny ze zmiennej tabeli.

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

SELECT col1, col2 FROM @tablevar

Oto link do całkiem dobrego dokumentu wyjaśniającego wszystkie różne sposoby rozwiązania problemu (chociaż wielu z nich nie można użyć, ponieważ nie można zmodyfikować istniejącej procedury składowanej).

Jak udostępniać dane między przechowywanymi procedurami

Odpowiedź Gulzara zadziała (jest udokumentowana w powyższym linku), ale będzie trudna do napisania (musisz podać wszystkie 80 nazw kolumn w instrukcji @tablevar (col1, ...). A w przyszłości jeśli kolumna zostanie dodana do schematu lub dane wyjściowe zostaną zmienione, konieczne będzie zaktualizowanie kodu lub wystąpi błąd.

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

Źródło:
http://stevesmithblog.com/blog/select-from-a-stored-procedure/

To działa dla mnie: (tzn. Potrzebuję tylko 2 kolumn z 30+ zwróconych przez sp_help_job)

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

Zanim to zadziała, musiałem uruchomić to:

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

.... aby zaktualizować sys.serverstabelę. (tzn. używanie odnośników w OPENQUERY wydaje się domyślnie wyłączone).

Ze względu na moje proste wymaganie nie natknąłem się na żaden z problemów opisanych w sekcji OPENQUERY doskonałego łącza Lance'a.

Rossini, jeśli chcesz dynamicznie ustawić te parametry wejściowe, wówczas użycie OPENQUERY staje się nieco bardziej skomplikowane:

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

Nie jestem pewien różnic (jeśli w ogóle) między używaniem sp_serveroptiondo bezpośredniej aktualizacji istniejących sys.serversodnośników, a używaniem sp_addlinkedserver(jak opisano w łączu Lance'a) do utworzenia duplikatu / aliasu.

Uwaga 1: Wolę OPENQUERY niż OPENROWSET, ponieważ OPENQUERY nie wymaga definicji ciągu połączenia w proc.

Uwaga 2: Powiedziawszy to wszystko: normalnie użyłbym INSERT ... EXEC :) Tak, to dodatkowe 10 minut pisania, ale jeśli mogę pomóc, wolę nie bić się z:
(a) cytatami w cudzysłowie cytaty i
(b) tabele sys i / lub podstępne konfiguracje z połączonym serwerem (np. w tych przypadkach muszę przedstawić swoją sprawę naszym wszechmocnym DBA :)

Jednak w tym przypadku nie mogłem użyć konstruktu INSERT ... EXEC, ponieważ sp_help_jobjuż go używa. („Nie można zagnieździć instrukcji INSERT EXEC.”)

Aby to osiągnąć, najpierw musisz utworzyć #test_tablepodobny do poniższego:

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

Teraz wykonaj procedurę i wprowadź wartość #test_table:

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

Teraz pobierasz wartość z #test_table:

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

Jeśli możesz zmodyfikować procedurę składowaną, możesz łatwo wprowadzić wymagane definicje kolumn jako parametr i użyć automatycznie utworzonej tabeli tymczasowej:

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

W takim przypadku nie trzeba ręcznie tworzyć tabeli tymczasowej - jest ona tworzona automatycznie. Mam nadzieję że to pomoże.

Warto wiedzieć, dlaczego jest to takie trudne. Procedura przechowywana może zwracać tylko tekst (drukować „tekst”), może zwracać wiele tabel lub może nie zwracać żadnych tabel.

Więc coś takiego SELECT * FROM (exec sp_tables) Table1 nie zadziała

(Zakładając, że SQL Server)

Jedynym sposobem pracy z wynikami procedury składowanej w T-SQL jest użycie INSERT INTO ... EXECskładni. Daje to możliwość wstawienia do tabeli tymczasowej lub zmiennej tabeli, a następnie wybrania potrzebnych danych.

Szybki hack polegałby na dodaniu nowego parametru '@Column_Name'i wywołaniu przez funkcję wywołującą nazwy kolumny do pobrania. W części zwrotnej sproc będziesz miał instrukcje if / else i zwróci tylko określoną kolumnę, a jeśli pusty - zwróci wszystko.

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

Jeśli robisz to w celu ręcznej weryfikacji danych, możesz to zrobić za pomocą LINQPad.

Utwórz połączenie z bazą danych w LinqPad, a następnie utwórz instrukcje C # podobne do następujących:

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

Odwołanie http://www.global-webnet.net/blogengine/post/2008/09/10/LINQPAD-Using-Stored-Procedures-Accessing-a-DataSet.aspx

W przypadku SQL Server uważam, że to działa dobrze:

Utwórz tabelę tymczasową (lub tabelę stałą, tak naprawdę nie ma znaczenia) i wstaw wstawkę do instrukcji względem procedury składowanej. Zestaw wyników SP powinien pasować do kolumn w tabeli, w przeciwnym razie wystąpi błąd.

Oto przykład:

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;

Otóż ​​to!

Jak wspomniano w pytaniu, trudno jest zdefiniować 80-stopniową tabelę temperatur przed wykonaniem procedury składowanej.

Tak więc odwrotnie jest zapełnianie tabeli na podstawie zestawu wyników procedury składowanej.

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

Jeśli pojawia się jakikolwiek błąd, musisz włączyć zapytania rozproszone ad hoc, wykonując następujące zapytanie.

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

Aby wykonać sp_configureoba parametry w celu zmiany opcji konfiguracji lub uruchomienia RECONFIGUREinstrukcji, musisz mieć uprawnienia na ALTER SETTINGSpoziomie serwera

Teraz możesz wybrać określone kolumny z wygenerowanej tabeli

SELECT col1, col2
FROM #temp

Spróbuj tego

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

Wiem, że wykonanie ze sp i wstawienie do tabeli temp lub zmiennej tabeli byłoby opcją, ale nie sądzę, że to twoje wymaganie. Zgodnie z twoim wymaganiem poniższe zapytanie powinno działać:

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

jeśli masz zaufane połączenie, użyj poniższej instrukcji zapytania:

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

jeśli pojawia się błąd, aby uruchomić powyższą instrukcję, wystarczy uruchomić tę instrukcję poniżej:

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

Mam nadzieję, że pomoże to komuś, kto stanie przed podobnym problemem. Jeśli ktoś chciałby spróbować z tabelą tymczasową lub zmienną tabelową, która powinna wyglądać tak poniżej, ale w tym scenariuszu powinieneś wiedzieć, ile kolumn zwraca Twój sp, to powinieneś utworzyć tyle kolumn w tabeli tymczasowej lub zmiennej tabelowej:

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

Dla każdego, kto ma SQL 2012 lub nowszy, byłem w stanie to osiągnąć za pomocą procedur składowanych, które nie są dynamiczne i za każdym razem mają te same kolumny wyjściowe.

Ogólny pomysł polega na tym, że tworzę zapytanie dynamiczne, aby tworzyć, wstawiać, wybierać i upuszczać tabelę tymczasową i wykonać ją po wygenerowaniu. Dynamicznie generuję tabelę temp, najpierw pobierając nazwy i typy kolumn z procedury składowanej .

Uwaga: istnieją znacznie lepsze, bardziej uniwersalne rozwiązania, które będą działać z mniejszą liczbą wierszy kodu, jeśli będziesz chciał / mógł zaktualizować SP lub zmienić konfigurację i używać OPENROWSET. Skorzystaj z poniższego opisu, jeśli nie masz innego wyjścia.

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)

Najłatwiejszy sposób, jeśli potrzebujesz tego tylko raz:

Eksportuj do programu Excel w Kreatorze importu i eksportu, a następnie zaimportuj ten program Excel do tabeli.

Utwórz widok dynamiczny i uzyskaj z niego wynik .......

CREATE PROCEDURE dbo.usp_userwise_columns_value
(
    @userid BIGINT
)
AS 
BEGIN
        DECLARE @maincmd NVARCHAR(max);
        DECLARE @columnlist NVARCHAR(max);
        DECLARE @columnname VARCHAR(150);
        DECLARE @nickname VARCHAR(50);

        SET @maincmd = '';
        SET @columnname = '';
        SET @columnlist = '';
        SET @nickname = '';

        DECLARE CUR_COLUMNLIST CURSOR FAST_FORWARD
        FOR
            SELECT columnname , nickname
            FROM dbo.v_userwise_columns 
            WHERE userid = @userid

        OPEN CUR_COLUMNLIST
        IF @@ERROR <> 0
            BEGIN
                ROLLBACK
                RETURN
            END   

        FETCH NEXT FROM CUR_COLUMNLIST
        INTO @columnname, @nickname

        WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @columnlist = @columnlist + @columnname + ','

                FETCH NEXT FROM CUR_COLUMNLIST
                INTO @columnname, @nickname
            END
        CLOSE CUR_COLUMNLIST
        DEALLOCATE CUR_COLUMNLIST  

        IF NOT EXISTS (SELECT * FROM sys.views WHERE name = 'v_userwise_columns_value')
            BEGIN
                SET @maincmd = 'CREATE VIEW dbo.v_userwise_columns_value AS SELECT sjoid, CONVERT(BIGINT, ' + CONVERT(VARCHAR(10), @userid) + ') as userid , ' 
                            + CHAR(39) + @nickname + CHAR(39) + ' as nickname, ' 
                            + @columnlist + ' compcode FROM dbo.SJOTran '
            END
        ELSE
            BEGIN
                SET @maincmd = 'ALTER VIEW dbo.v_userwise_columns_value AS SELECT sjoid, CONVERT(BIGINT, ' + CONVERT(VARCHAR(10), @userid) + ') as userid , ' 
                            + CHAR(39) + @nickname + CHAR(39) + ' as nickname, ' 
                            + @columnlist + ' compcode FROM dbo.SJOTran '
            END

        --PRINT @maincmd
        EXECUTE sp_executesql @maincmd
END

-----------------------------------------------
SELECT * FROM dbo.v_userwise_columns_value

Wytnij i wklej oryginalny SP i usuń wszystkie kolumny oprócz 2, których chcesz. Lub. Chciałbym przywrócić zestaw wyników, zmapować go do odpowiedniego obiektu biznesowego, a następnie LINQ wydzielić dwie kolumny.