Selecionar colunas do conjunto de resultados do procedimento armazenado

Eu tenho um procedimento armazenado que retorna 80 colunas e 300 linhas. Eu quero escrever um select que obtenha 2 dessas colunas. Algo como

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

Quando usei a syntax acima, recebo o erro:

“Nome da Coluna Inválido”.

Eu sei que a solução mais fácil seria alterar o procedimento armazenado, mas não o escrevi e não posso alterá-lo.

Existe alguma maneira de fazer o que eu quero?

  • Eu poderia fazer uma tabela temporária para colocar os resultados, mas porque existem 80 colunas, então eu precisaria fazer uma tabela temporária de 80 colunas apenas para obter 2 colunas. Eu queria evitar rastrear todas as colunas retornadas.

  • Eu tentei usar WITH SprocResults AS .... como sugerido por Mark, mas eu tenho 2 erros

    Sintaxe incorreta perto da palavra-chave ‘EXEC’.
    Sintaxe incorreta perto de ‘)’.

  • Eu tentei declarar uma variável de tabela e recebi o seguinte erro

    Erro de inserção: o nome da coluna ou o número de valores fornecidos não corresponde à definição da tabela

  • Se eu tentar
    SELECT * FROM EXEC MyStoredProc 'param1', 'param2'
    Eu recebo o erro:

    Sintaxe incorreta perto da palavra-chave ‘exec’.

Você pode dividir a consulta? Insira os resultados do proc armazenados em uma variável de tabela ou em uma tabela temporária. Em seguida, selecione as duas colunas da variável table.

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

Aqui está um link para um documento muito bom explicando todas as maneiras diferentes de resolver seu problema (embora muitos deles não possam ser usados, já que você não pode modificar o procedimento armazenado existente).

Como compartilhar dados entre stored procedures

A resposta de Gulzar irá funcionar (está documentada no link acima), mas será um incômodo para escrever (você precisará especificar todos os 80 nomes de colunas em sua instrução @tablevar (col1, …). se uma coluna for adicionada ao esquema ou a saída for alterada, ela precisará ser atualizada em seu código ou ocorrerá um erro.

 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 

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

Isso funciona para mim: (ou seja, eu só preciso de 2 colunas de 30+ retornadas por sp_help_job )

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

Antes que isso funcionasse, eu precisava executar isso:

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

…. para atualizar a tabela sys.servers . (Por exemplo, usar uma auto-referência dentro de OPENQUERY parece estar desabilitado por padrão).

Para minha exigência simples, não encontrei nenhum dos problemas descritos na seção OPENQUERY do excelente link de Lance.

Rossini, se você precisar definir dinamicamente esses parâmetros de input, o uso de OPENQUERY se tornará um pouco mais complicado:

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

Eu não tenho certeza das diferenças (se houver) entre usar sp_serveroption para atualizar a auto-referência sys.servers existente diretamente, vs. usando sp_addlinkedserver (como descrito no link de Lance) para criar um duplicado / alias.

Nota 1: Eu prefiro o OPENQUERY sobre o OPENROWSET, dado que o OPENQUERY não requer a definição da string de conexão dentro do proc.

Nota 2: Tendo dito tudo isso: normalmente eu usaria o INSERT … EXEC 🙂 Sim, são 10 minutos de digitação extra, mas se eu puder evitar, prefiro não mexer com:
(a) citações entre aspas entre aspas e
(b) tabelas sys, e / ou auto-referenciação sorrateira Configurações do Servidor Vinculado (ou seja, para isso, eu preciso defender meu caso para todos os nossos DBAs poderosos 🙂

No entanto, neste exemplo, não pude usar uma construção INSERT … EXEC, já que sp_help_job já está usando uma. (“Uma instrução INSERT EXEC não pode ser aninhada.”)

Para conseguir isso, primeiro você cria um #test_table como abaixo:

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

Agora execute o procedimento e coloque o valor em #test_table :

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

Agora você busca o valor de #test_table :

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

Se você puder modificar seu procedimento armazenado, poderá colocar facilmente as definições de colunas necessárias como um parâmetro e usar uma tabela temporária criada automaticamente:

 CREATE PROCEDURE sp_GetDiffDataExample @columnsStatement NVARCHAR(MAX) -- required columns statement (eg "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 

Neste caso, você não precisa criar uma tabela temporária manualmente – ela é criada automaticamente. Espero que isto ajude.

Pode ser útil saber por que isso é tão difícil. Um procedimento armazenado só pode retornar texto (imprimir ‘texto’), ou pode retornar várias tabelas, ou pode retornar nenhuma tabela em todos os.

Então, algo como SELECT * FROM (exec sp_tables) Table1 não funcionará

(Assumindo o SQL Server)

A única maneira de trabalhar com os resultados de um procedimento armazenado no T-SQL é usar a syntax INSERT INTO ... EXEC . Isso lhe dá a opção de inserir em uma tabela temporária ou uma variável de tabela e a partir daí selecionar os dados que você precisa.

Um hack rápido seria adicionar um novo parâmetro '@Column_Name' e fazer com que a function de chamada defina o nome da coluna a ser recuperada. Na parte de retorno do seu sproc, você teria instruções if / else e retornaria apenas a coluna especificada ou, se vazio, retornaria todas.

 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 

Se você estiver fazendo isso para validação manual dos dados, poderá fazer isso com o LINQPad.

Crie uma conexão com o database no LinqPad e crie instruções em C # semelhantes às seguintes:

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

Referência http://www.global-webnet.net/blogengine/post/2008/09/10/LINQPAD-Using-Stored-Procedures-Accessing-a-DataSet.aspx

Para o SQL Server, acho que isso funciona bem:

Criar uma tabela temporária (ou tabela permanente, realmente não importa) e fazer uma inserção na instrução contra o procedimento armazenado. O conjunto de resultados do SP deve corresponder às colunas da sua tabela, caso contrário, você receberá um erro.

Aqui está um exemplo:

 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; 

É isso aí!

tente isso

 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 

Como foi mencionado na pergunta, é difícil definir a tabela temporária de 80 colunas antes de executar o procedimento armazenado.

Portanto, o inverso disso é preencher a tabela com base no conjunto de resultados do procedimento armazenado.

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

Se você está recebendo algum erro, é necessário ativar as consultas distribuídas ad hoc executando a seguinte consulta.

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

Para executar sp_configure com os dois parâmetros para alterar uma opção de configuração ou para executar a instrução RECONFIGURE , você deve receber a permissão de nível de servidor ALTER SETTINGS

Agora você pode selecionar suas colunas específicas da tabela gerada

 SELECT col1, col2 FROM #temp 

Você já tentou isso:

  • criar uma function que deve chamar isso StoredProcedure
  • use um cursor para armazenar os resultados retornados do StoredProcedure
  • loop esse cursor, para que você possa imprimir / armazenar as duas colunas necessárias na tabela temporária (com apenas 2 colunas)
  • e use essa tabela para selecionar os resultados

Eu cortaria e colaria o SP original e excluiria todas as colunas, exceto as 2 que você deseja. Ou. Eu traria o conjunto de resultados de volta, mapeá-lo para um object de negócios adequado, em seguida, LINQ as duas colunas.

Maneira mais fácil de fazer, se você só precisa desta vez:

Exporte para excel no assistente de Importação e Exportação e importe este excel para uma tabela.

    Intereting Posts