sql-server - una - output procedimiento almacenado sql server
Recuperar definición de columna para conjunto de resultados de procedimiento almacenado (5)
Estoy trabajando con procedimientos almacenados en SQL Server 2008 y he aprendido que tengo que INSERT INTO UNA tabla temporal que ha sido predefinida para trabajar con los datos. Está bien, excepto ¿cómo averiguo cómo definir mi tabla temporal, si no soy el que escribió el procedimiento almacenado aparte de enumerar su definición y leer el código?
Por ejemplo, ¿cómo se vería mi tabla temporal para ''EXEC sp_stored_procedure''? Ese es un procedimiento simple almacenado, y probablemente podría adivinar los tipos de datos, pero parece que debe haber una manera de simplemente leer el tipo y la longitud de las columnas devueltas al ejecutar el procedimiento.
Aquí hay un código que escribí. La idea es (como dijo otra persona) obtener el código SP, modificarlo y ejecutarlo. Sin embargo, mi código no cambia el SP original.
Primer paso, obtener la definición del SP, quitar la parte ''Crear'' y deshacerse del ''AS'' después de la declaración de parámetros, si existe.
Declare @SPName varchar(250)
Set nocount on
Declare @SQL Varchar(max), @SQLReverse Varchar(MAX), @StartPos int, @LastParameterName varchar(250) = '''', @TableName varchar(36) = ''A'' + REPLACE(CONVERT(varchar(36), NewID()), ''-'', '''')
Select * INTO #Temp from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME = ''ADMIN_Sync_CompareDataForSync''
if @@ROWCOUNT > 0
BEGIN
Select @SQL = REPLACE(ROUTINE_DEFINITION, ''CREATE PROCEDURE ['' + ROUTINE_SCHEMA + ''].['' + ROUTINE_NAME + '']'', ''Declare'')
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_NAME = @SPName
Select @LastParameterName = PARAMETER_NAME + '' '' + DATA_TYPE +
CASE WHEN CHARACTER_MAXIMUM_LENGTH is not null THEN ''('' +
CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN ''MAX'' ELSE CONVERT(varchar,CHARACTER_MAXIMUM_LENGTH) END + '')'' ELSE '''' END
from #Temp
WHERE ORDINAL_POSITION =
(Select MAX(ORDINAL_POSITION)
From #Temp)
Select @StartPos = CHARINDEX(@LastParameterName, REPLACE(@SQL, '' '', '' ''), 1) + LEN(@LastParameterName)
END
else
Select @SQL = REPLACE(ROUTINE_DEFINITION, ''CREATE PROCEDURE ['' + ROUTINE_SCHEMA + ''].['' + ROUTINE_NAME + '']'', '''') from INFORMATION_SCHEMA.ROUTINES where ROUTINE_NAME = @SPName
DROP TABLE #Temp
Select @StartPos = CHARINDEX(''AS'', UPPER(@SQL), @StartPos)
Select @SQL = STUFF(@SQL, @StartPos, 2, '''')
(Tenga en cuenta la creación de un nuevo nombre de tabla basado en un identificador único) Ahora busque la última palabra ''De'' en el código, suponiendo que este es el código que hace la selección que devuelve el conjunto de resultados.
Select @SQLReverse = REVERSE(@SQL)
Select @StartPos = CHARINDEX(''MORF'', UPPER(@SQLReverse), 1)
Cambie el código para seleccionar el conjunto de resultados en una tabla (la tabla basada en el identificador único)
Select @StartPos = LEN(@SQL) - @StartPos - 2
Select @SQL = STUFF(@SQL, @StartPos, 5, '' INTO '' + @TableName + '' FROM '')
EXEC (@SQL)
El conjunto de resultados ahora está en una tabla, ¡no importa si la tabla está vacía!
Permite obtener la estructura de la tabla
Select * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
Ahora puedes hacer tu magia con esto
No te olvides de dejar caer esa mesa única
Select @SQL = ''drop table '' + @TableName
Exec (@SQL)
¡Espero que esto ayude!
Entonces digamos que tienes un procedimiento almacenado en tempdb:
USE tempdb;
GO
CREATE PROCEDURE dbo.my_procedure
AS
BEGIN
SET NOCOUNT ON;
SELECT foo = 1, bar = ''tooth'';
END
GO
Hay una manera bastante intrincada de determinar los metadatos que generará el procedimiento almacenado. Hay varias advertencias, incluido que el procedimiento solo puede dar salida a un único conjunto de resultados, y que se hará una mejor estimación sobre el tipo de datos si no se puede determinar con precisión. Requiere el uso de OPENQUERY y un servidor vinculado de bucle invertido con la propiedad ''DATA ACCESS'' establecida en verdadero. Puede verificar sys.servers para ver si ya tiene un servidor válido, pero solo creemos uno llamado loopback llamado manualmente:
EXEC master..sp_addlinkedserver
@server = ''loopback'',
@srvproduct = '''',
@provider = ''SQLNCLI'',
@datasrc = @@SERVERNAME;
EXEC master..sp_serveroption
@server = ''loopback'',
@optname = ''DATA ACCESS'',
@optvalue = ''TRUE'';
Ahora que puede consultar esto como un servidor vinculado, puede usar el resultado de cualquier consulta (incluida una llamada a un procedimiento almacenado) como SELECT regular. Entonces puede hacer esto (tenga en cuenta que el prefijo de la base de datos es importante, de lo contrario recibirá el error 11529 y 2812):
SELECT * FROM OPENQUERY(loopback, ''EXEC tempdb.dbo.my_procedure;'');
Si podemos realizar un SELECT * , también podemos realizar un SELECT * INTO :
SELECT * INTO #tmp FROM OPENQUERY(loopback, ''EXEC tempdb.dbo.my_procedure;'');
Y una vez que esa tabla #tmp existe, podemos determinar los metadatos diciendo (suponiendo SQL Server 2005 o superior):
SELECT c.name, [type] = t.name, c.max_length, c.[precision], c.scale
FROM sys.columns AS c
INNER JOIN sys.types AS t
ON c.system_type_id = t.system_type_id
AND c.user_type_id = t.user_type_id
WHERE c.[object_id] = OBJECT_ID(''tempdb..#tmp'');
(Si está usando SQL Server 2000, puede hacer algo similar con syscolumns, pero no tengo una instancia de 2000 a mano para validar una consulta equivalente).
Resultados:
name type max_length precision scale
--------- ------- ---------- --------- -----
foo int 4 10 0
bar varchar 5 0 0
En Denali, esto será mucho, mucho, mucho más fácil. De nuevo, todavía hay una limitación del primer conjunto de resultados, pero no es necesario configurar un servidor vinculado y pasar por todos esos aros. Usted puede decir:
DECLARE @sql NVARCHAR(MAX) = N''EXEC tempdb.dbo.my_procedure;'';
SELECT name, system_type_name
FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1);
Resultados:
name system_type_name
--------- ----------------
foo int
bar varchar(5)
Hasta Denali, sugiero que sería más fácil arremangarse y descubrir los tipos de datos por su cuenta. No solo porque es tedioso seguir los pasos anteriores, sino también porque es mucho más probable que realice una conjetura correcta (o al menos más precisa) que la del motor, ya que el tipo de datos adivina que las marcas del motor se basarán en el tiempo de ejecución salida, sin ningún conocimiento externo del dominio de valores posibles. Este factor también seguirá siendo válido en Denali, por lo que no debe tener la impresión de que las nuevas características de descubrimiento de metadatos son un todo, sino que hacen que lo anterior sea menos tedioso.
Ah, y para algunos otros posibles problemas con OPENQUERY , vea el artículo de Erland Sommarskog aquí:
Parece que en SQL 2012 hay un nuevo SP para ayudar con esto.
exec sp_describe_first_result_set N''PROC_NAME''
Si trabajas en un entorno con derechos restringidos donde cosas como el servidor vinculado a loopback parecen magia negra y definitivamente no son "de ninguna manera", pero tienes unos pocos derechos sobre el esquema y solo un par de procedimientos almacenados para procesar, hay una muy simple solución.
Puede usar la útil sintaxis SELECT INTO , que creará una nueva tabla con el conjunto de resultados de una consulta.
Digamos que su procedimiento contiene la siguiente consulta Select:
SELECT x, y, z
FROM MyTable t INNER JOIN Table2 t2 ON t.id = t2.id...
En lugar de reemplazarlo por:
SELECT x, y, z
INTO MyOutputTable
FROM MyTable t INNER JOIN Table2 t2 ON t.id = t2.id...
Cuando lo ejecute, creará una nueva tabla MyOutputTable con los resultados devueltos por la consulta.
Solo tiene que hacer clic con el botón derecho sobre su nombre para obtener la definición de la tabla.
Eso es todo !
SELECT INTO solo requiere la capacidad de crear nuevas tablas y también funciona con tablas temporales (SELECT ... INTO #MyTempTable), pero podría ser más difícil recuperar la definición.
Sin embargo, por supuesto, si necesita recuperar la definición de salida de miles de SP, no es la forma más rápida :)
Una forma menos sofisticada (que podría ser suficiente en algunos casos): edite su SP original, después del SELECT final y antes de la cláusula FROM, agregue INSERT INTO tmpTable para guardar el resultado del SP en tmpTable.
Ejecute el SP modificado, preferiblemente con parámetros significativos para obtener datos reales. Restaure el código original del procedimiento.
Ahora puede obtener el script de tmpTable desde SQL server management studio o consultar sys.columns para obtener las descripciones de los campos.