sql server - hacer - Seleccionar columnas del conjunto de resultados del procedimiento almacenado

sql server select de un procedure

Question

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

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

Cuando utilicé la sintaxis anterior obtengo 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 manera de hacer lo que quiero?

• Podría hacer una tabla temporal para colocar 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 .... como sugirió 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 proporcionados no coincide con la definición de la tabla

• Si lo intento
  SELECT * FROM EXEC MyStoredProc ''param1'', ''param2''
  Me sale el error
  

  Sintaxis incorrecta cerca de la palabra clave ''exec''.

Answer 1

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

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

SELECT col1, col2 FROM @tablevar

Answer 2

(Asumiendo SQL Server)

La única forma de trabajar con los resultados de un procedimiento almacenado en T-SQL es usar la sintaxis 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.

Answer 3

Aquí hay un enlace a un documento bastante bueno que explica las diferentes maneras de resolver su problema (aunque no se pueden usar muchos de ellos 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 anterior), pero será una molestia escribir (deberá especificar los 80 nombres de columna en su declaración @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.

Answer 4

Como se mencionó en la pregunta, es difícil definir la tabla temporal de 80 columnas antes de ejecutar el procedimiento almacenado.

Entonces, al revés, esto es rellenar 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 ejecutando 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 para ejecutar la instrucción RECONFIGURE , se le debe otorgar el permiso de nivel de servidor ALTER SETTINGS

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

SELECT col1, col2
FROM #temp

Answer 5

Cortaría y pegaría el SP original y eliminaría todas las columnas excepto las 2 que desea. O. Devolvería el conjunto de resultados, lo asignaría a un objeto de negocio adecuado, y luego LINQ sacaría las dos columnas.

Answer 6

Esto me funciona: (es decir, solo necesito 2 columnas de las 30+ que devuelve 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 auto-referencia en OPENQUERY parece estar deshabilitado por defecto).

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

Rossini, si necesita configurar 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 usar sp_serveroption para actualizar la auto-referencia sys.servers existente directamente, en lugar de usar sp_addlinkedserver (como se describe en el enlace de Lance) para crear un duplicado / alias.

Nota 1: Prefiero OPENQUERY sobre OPENROWSET, dado que OPENQUERY no requiere la definición de cadena de conexión dentro del proc.

Nota 2: Habiendo dicho todo esto: normalmente solo usaría INSERT ... EJEC :) Sí, son 10 minutos más de escritura, pero si puedo evitarlo, prefiero no deambular con:
(a) comillas dentro de comillas dentro de comillas, y
(b) tablas de sys, y / o configuraciones de servidores vinculados que hacen referencia a sí mismas (es decir, para estas, tengo que defender mi caso ante nuestros DBAs todopoderosos :)

Sin embargo, en este caso, no pude usar una construcción INSERT ... EXEC, ya que sp_help_job ya está usando una. ("Una sentencia INSERT EXEC no puede estar anidada")

Answer 7

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

Exporte para sobresalir en el Asistente de importación y exportación y luego importe este excel en una tabla.

Answer 8

Para SQL Server, encuentro que esto funciona bien:

Cree una tabla temporal (o tabla permanente, en realidad no importa) y realice una inserción en la declaración en 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!

Answer 9

Para lograr esto, primero creas una #test_table como a continuación:

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

Ahora ejecuta el procedimiento y coloca el valor en #test_table :

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

Ahora #test_table el valor de #test_table :

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

Answer 10

Podría ser útil saber por qué esto es tan difícil. Un procedimiento almacenado solo puede devolver texto (imprimir ''texto''), o puede devolver varias tablas, o puede no devolver ninguna tabla.

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

Answer 11

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 de C # similares a las siguientes:

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

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

Answer 12

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

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

Answer 13

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 la columna que se recuperará. En la parte de devolución de su sproc, tendría declaraciones if / else y devolvería solo la columna especificada, o si estaba vacía, devolvería 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

Answer 14

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

Answer 15

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/