Seleccionar columnas del conjunto de resultados del procedimiento almacenado

Tengo un procedimiento almacenado que devuelve 80 columnas y 300 filas. Quiero escribir una selección que obtenga 2 de esas columnas. Algo como

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

Cuando utilicé la syntax anterior, recibí el error:

“Nombre de columna inválido”.

Sé que la solución más fácil sería cambiar el procedimiento almacenado, pero no lo escribí, y no puedo cambiarlo.

¿Hay alguna forma de hacer lo que quiero?

  • Podría hacer una tabla temporal para poner los resultados, pero como hay 80 columnas, necesitaría hacer una tabla temporal de 80 columnas solo para obtener 2 columnas. Quería evitar rastrear todas las columnas que se devuelven.

  • Intenté usar WITH SprocResults AS .... según lo sugerido por Mark, pero obtuve 2 errores

    Sintaxis incorrecta cerca de la palabra clave ‘EXEC’.
    Sintaxis incorrecta cerca ‘)’.

  • Intenté declarar una variable de tabla y obtuve el siguiente error

    Error de inserción: el nombre de la columna o el número de valores suministrados no coincide con la definición de la tabla

  • Si bash
    SELECT * FROM EXEC MyStoredProc 'param1', 'param2'
    Me sale el error:

    Sintaxis incorrecta cerca de la palabra clave ‘exec’.

¿Puedes dividir la consulta? Inserte los resultados de proceso almacenados en una variable de tabla o una tabla temporal. Luego, selecciona las 2 columnas de la variable de la tabla.

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

Aquí hay un enlace a un documento bastante bueno que explica todas las formas diferentes de resolver su problema (aunque muchas de ellas no se pueden usar ya que no puede modificar el procedimiento almacenado existente).

Cómo compartir datos entre procedimientos almacenados

La respuesta de Gulzar funcionará (está documentada en el enlace de arriba), pero va a ser una tarea difícil de escribir (tendrá que especificar los 80 nombres de columna en su statement @tablevar (col1, …). Y en el futuro si se agrega una columna al esquema o se modifica la salida, deberá actualizarse en su código o se producirá un error.

 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 

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

Esto funciona para mí: (es decir, solo necesito 2 columnas de las más de 30 devueltas 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 de que esto funcionara, necesitaba ejecutar esto:

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

…. para actualizar la tabla sys.servers . (es decir, el uso de una autorreferencia dentro de OPENQUERY parece estar deshabilitado de manera predeterminada).

Para mi simple requerimiento, me topé con ninguno de los problemas descritos en la sección OPENQUERY del excelente enlace de Lance.

Rossini, si necesita establecer dinámicamente esos parámetros de entrada, entonces el uso de OPENQUERY se vuelve un poco más 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); 

No estoy seguro de las diferencias (si las hay) entre el uso de sp_serveroption para actualizar sys.servers autorreferencia de sys.servers existente, y el uso de sp_addlinkedserver (como se describe en el enlace de Lance) para crear un duplicado / alias.

Nota 1: prefiero OPENQUERY a OPENROWSET, dado que OPENQUERY no requiere la definición de cadena de conexión dentro del proceso.

Nota 2: Habiendo dicho todo esto: normalmente usaría INSERT … EXEC 🙂 Sí, son 10 minutos más de tipeo, pero si puedo evitarlo, prefiero no involucrarme con:
(a) citas entre comillas dentro de las citas, y
(b) tablas de sys, y / o configuraciones de servidor vinculadas autorreferenciadas furtivas (es decir, para estas, tengo que defender mi caso a nuestros DBA todopoderosos 🙂

Sin embargo, en este caso, no pude usar un constructo INSERT … EXEC, ya que sp_help_job ya está usando uno. (“Una statement INSERT EXEC no se puede anidar”).

Para lograr esto, primero creas una #test_table como la siguiente:

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

Ahora ejecuta el procedimiento y pon valor en #test_table :

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

Ahora recuperas el valor de #test_table :

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

Si puede modificar su procedimiento almacenado, puede colocar fácilmente las definiciones de columnas requeridas como un parámetro y usar una tabla temporal creada automáticamente:

 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 

En este caso, no es necesario crear una tabla temporal manualmente, sino que se crea automáticamente. Espero que esto ayude.

Puede ser útil saber por qué es tan difícil. Un procedimiento almacenado solo puede devolver texto (imprimir ‘texto’), o puede devolver varias tablas, o puede devolver ninguna tabla.

Así que algo como SELECT * FROM (exec sp_tables) Table1 no funcionará

(Asumiendo SQL Server)

La única forma de trabajar con los resultados de un procedimiento almacenado en T-SQL es usar la syntax INSERT INTO ... EXEC . Eso le da la opción de insertar en una tabla temporal o una variable de tabla y desde allí seleccionar los datos que necesita.

Un truco rápido sería agregar un nuevo parámetro '@Column_Name' y hacer que la función de llamada defina el nombre de columna que se recuperará. En la parte de retorno de su sproc, tendría declaraciones if / else y devolvería solo la columna especificada, o si está vacía, devuelva todo.

 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 

Si está haciendo esto para la validación manual de los datos, puede hacerlo con LINQPad.

Cree una conexión a la base de datos en LinqPad y luego cree declaraciones C # similares a las siguientes:

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

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

Para SQL Server, creo que esto funciona bien:

Crear una tabla temporal (o tabla permanente, realmente no importa), y hacer una inserción en la statement contra el procedimiento almacenado. El conjunto de resultados del SP debe coincidir con las columnas de su tabla; de lo contrario, obtendrá un error.

Aquí hay un ejemplo:

 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; 

¡Eso es!

prueba esto

 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 se ha mencionado en la pregunta, es difícil definir la tabla de temp. De 80 columnas antes de ejecutar el procedimiento almacenado.

Así que al revés, esto es llenar la tabla en función del conjunto de resultados del procedimiento almacenado.

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

Si recibe algún error, debe habilitar las consultas distribuidas ad hoc mediante la ejecución de la siguiente consulta.

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

Para ejecutar sp_configure con ambos parámetros para cambiar una opción de configuración o ejecutar la instrucción RECONFIGURE , se le debe otorgar el permiso ALTER SETTINGS a nivel de servidor

Ahora puede seleccionar sus columnas específicas de la tabla generada

 SELECT col1, col2 FROM #temp 

¿Has probado esto?

  • crear una función que debería llamar a este StoredProcedure
  • use un cursor para almacenar los resultados devueltos por ese StoredProcedure
  • recorra ese cursor, para que pueda imprimir / almacenar esas dos columnas que necesita en la tabla temporal (solo con 2 columnas)
  • y luego usa esa tabla para seleccionar los resultados

Cortaría y pegaría el SP original y borraría todas las columnas excepto las 2 que quisiera. O. Retiraría el conjunto de resultados, lo correlacionaría con un objeto comercial adecuado y luego saldría de las dos columnas.

La manera más fácil de hacerlo si solo necesitas esto una vez:

Exporte para excel en el asistente Importar y Exportar y luego importe este Excel en una tabla.