sql - usar - ¿Recuperar nombres de columna y tipos de un procedimiento almacenado?

retornar valor stored procedure sql server

Question

Posible duplicado:
Recuperar la definición de la columna para el conjunto de resultados del procedimiento almacenado

Uso el siguiente SQL para obtener nombres y tipos de columnas para una tabla o vista:

DECLARE @viewname varchar (250);

select a.name as colname,b.name as typename 
from syscolumns a, systypes b -- GAH!
where a.id = object_id(@viewname) 
and a.xtype=b.xtype 
and b.name <> ''sysname''

¿Cómo hago algo similar para las columnas de salida de un procedimiento almacenado?

Answer 1

¿Está intentando devolver todos los procedimientos almacenados y todos sus parámetros? Algo como esto debería funcionar para eso.

select * from information_schema.parameters

Si necesita obtener las columnas devueltas de un procedimiento almacenado, eche un vistazo aquí:

Obtenga nombres / tipos de columna devueltos desde un procedimiento almacenado

Answer 2

[Me acabo de dar cuenta de que he respondido esta pregunta antes ]

Hacer esto para un procedimiento almacenado es mucho más complicado de lo que es para una vista o tabla. Uno de los problemas es que un procedimiento almacenado puede tener múltiples rutas de código diferentes dependiendo de los parámetros de entrada e incluso de cosas que no puede controlar, como el estado del servidor, la hora del día, etc. Entonces, por ejemplo, lo que esperaría ver como la salida de este procedimiento almacenado? ¿Qué pasa si hay múltiples conjuntos de resultados independientemente de los condicionales?

CREATE PROCEDURE dbo.foo
  @bar INT
AS
BEGIN
  SET NOCOUNT ON;

  IF @bar = 1
    SELECT a, b, c FROM dbo.blat;
  ELSE
    SELECT d, e, f, g, h FROM dbo.splunge;
END
GO

Si su procedimiento almacenado no tiene rutas de código y confía en que siempre verá el mismo conjunto de resultados (y puede determinar de antemano qué valores deben suministrarse si un procedimiento almacenado tiene parámetros no opcionales), veamos un ejemplo sencillo:

CREATE PROCEDURE dbo.bar
AS
BEGIN
  SET NOCOUNT ON;

  SELECT a = ''a'', b = 1, c = GETDATE();
END
GO

FMTONLY

Una forma es hacer algo como esto:

SET FMTONLY ON;
GO
EXEC dbo.bar;

Esto le dará un conjunto de resultados vacío y su aplicación cliente puede echar un vistazo a las propiedades de ese conjunto de resultados para determinar los nombres de columna y los tipos de datos.

Ahora, hay muchos problemas con SET FMTONLY ON; que no voy a entrar aquí, pero al menos debe tenerse en cuenta que este comando está en desuso , por una buena razón. También tenga cuidado de SET FMTONLY OFF; cuando haya terminado, o se preguntará por qué crea un procedimiento almacenado correctamente pero luego no puede ejecutarlo. Y no, no te lo advierto porque me acaba de pasar. Honesto. :-)

OPENQUERY

Al crear un servidor vinculado de bucle invertido, puede utilizar herramientas como OPENQUERY para ejecutar un procedimiento almacenado, pero devolver un conjunto de resultados compositivos (bueno, acepte eso como una definición extremadamente vaga) que puede inspeccionar. Primero cree un servidor de bucle invertido (esto supone una instancia local llamada FOO ):

USE master;
GO
EXEC sp_addlinkedserver @server = N''./FOO'', @srvproduct=N''SQL Server''
GO
EXEC sp_serveroption @server=N''./FOO'', @optname=N''data access'', 
  @optvalue=N''true'';

Ahora podemos seguir el procedimiento anterior y enviarlo a una consulta como esta:

SELECT * INTO #t 
FROM OPENQUERY([./FOO], ''EXEC dbname.dbo.bar;'')
WHERE 1 = 0;

SELECT c.name, t.name
FROM tempdb.sys.columns AS c
INNER JOIN sys.types AS t
ON c.system_type_id = t.system_type_id
WHERE c.[object_id] = OBJECT_ID(''tempdb..#t'');

Esto ignora los tipos de alias (anteriormente conocidos como tipos de datos definidos por el usuario) y también puede mostrar dos filas para las columnas definidas como, por ejemplo, sysname . Pero a partir de lo anterior se produce:

name   name
----   --------
b      int
c      datetime
a      varchar

Obviamente, hay más trabajo que hacer aquí: varchar no muestra la longitud, y tendrás que obtener precisión / escala para otros tipos como datetime2 , time y decimal . Pero eso es un comienzo.

SQL Server 2012

Existen algunas funciones nuevas en SQL Server 2012 que facilitan mucho el descubrimiento de metadatos. Para el procedimiento anterior podemos hacer lo siguiente:

SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set_for_object
(
  OBJECT_ID(''dbo.bar''), 
  NULL
);

Entre otras cosas, esto realmente proporciona precisión y escala y resuelve los tipos de alias para nosotros. Para el procedimiento anterior esto produce:

name   system_type_name
----   ----------------
a      varchar(1)
b      int
c      datetime

No hay mucha diferencia visual, pero cuando empiece a acceder a los diferentes tipos de datos con precisión y escala, apreciará el trabajo adicional que esta función hace por usted.

El inconveniente: en SQL Server 2012 al menos estas funciones solo funcionan para el primer conjunto de resultados (como lo indica el nombre de la función).