En este artículo ofrezco varias respuestas a estas preguntas, y analizo las ventajas y desventajas de esas soluciones. Aunque ciertos métodos exigen que reescribas tus procedimientos, esto suele ser la mejor solución, siempre que tengas esta posibilidad. Aquí está un resumen de los varios métodos.
Estas soluciones exigen que reescribas el procedimiento:
- Parámetros OUTPUT. Posible sólo en un caso específico, pero a veces se olvida esta opción.
- Funciones de valores de tabla. Suele ser la mejor solución, pero debes tener el SQL Server 2000.
- Compartir tablas temporales. Posible en todas las versiones del MSSQL.
Estas soluciones no exigen que lo reescribas:
- INSERT-EXEC. Debes tener el SQL Server 6.5 o una versión más reciente.
- OPENQUERY. Debes tener el SQL Server 7 o una versión más reciente.
Parámetros OUTPUT
Aunque este método sirve únicamente para los procedimientos almacenados que devuelven una sola fila, a veces se olvida esta posibilidad. Miramos el procedimiento siguiente:
CREATE PROCEDURE insert_customer @name nvarchar(50), @address nvarchar(50), @city nvarchar(50) AS DECLARE @cust_id int BEGIN TRANSACTION SELECT @cust_id = coalesce(MAX(cust_id), 0) + 1 FROM customers (UPDLOCK) INSERT customers (cust_id, name, address, city) VALUES (@cust_id, @name, @address, @city) COMMIT TRANSACTION SELECT @cust_idEl procedimiento almacenado inserta una fila en una tabla, y luego devuelve el ID de la nueva fila.
Reescribimos el procedimiento así:
CREATE PROCEDURE insert_customer @name nvarchar(50), @address nvarchar(50), @city nvarchar(50), @cust_id int OUTPUT AS BEGIN TRANSACTION SELECT @cust_id = coalesce(MAX(cust_id), 0) + 1 FROM customers (UPDLOCK) INSERT customers (cust_id, name, address, city) VALUES (@cust_id, @name, @address, @city) COMMIT TRANSACTIONLa nueva versión tiene dos gran ventajas:
- Es muy sencillo ejecutar insert_customer desde otro procedimiento almacenado. Pero no olvides que en el TSQL debes incluir la palabra clave OUTPUT al ejecutarlo:
EXEC insert_customer @name, @address, @city, @cust_id OUTPUT
- Es mucho más rápido devolver un valor mediante un parámetro de salida que usar un conjunto de resultados. Si no ejecutas el procedimiento muy frecuentemente, puede que el tiempo de ejecución sea más o menos igual, pero si tienes que añadir 2000 clientes pues la diferencia sería más evidente. En el ejemplar de febrero 2001 de SQL Server Professional, Craig Utley presentó los resultados de una prueba benchmark en la que comparó el rendimiento de OUTPUT y un conjunto de resultados.
NB: en este ejemplo hay un solo parámetro de salida, pero un procedimiento almacenado puede tener varios.
Si utilizas este método, debes saber cómo manejar parámetros de salida desde tu biblioteca de cliente, es decir desde ADO, ODBC, JDBC etc. Ya que este artículo no aborda el tema de programación de aplicaciones, te dejo a tí la investigación de los detalles. Pero quiero aclarar que esta solución fuciona con todas las bibliotecas de cliente.
Funciones de valores de tabla
Funciones definidas por el usuario existen en el SQL Server desde la versión 2000, y son de tres tipos: 1) funciones escalares 2) funciones de valores de tabla en línea 3) funciones de valores de tabla de múltiples instrucciones. Aquí, nos interesan sólo las funciones de valores de tabla.
No quiero entrar en detalle sobre las funciones definidas por el usuario, pues te doy unos ejemplos básicos. Para saber más, consulta la sección CREATE FUNCTION en Books Online.
Este ejemplo de una función de valores de tabla en línea viene de Books Online:
CREATE FUNCTION SalesByStore (@storeid varchar(30)) RETURNS TABLE AS RETURN (SELECT title, qty FROM sales s, titles t WHERE s.stor_id = @storeid and t.title_id = s.title_id)Para ejecutarla, se utiliza una consulta tal como la siguiente:
SELECT * FROM SalesByStore('6380')Ya ves que puedes utilizar la función como si fuera una tabla o vista. El optimizador de consultas procesa una función en línea de la misma manera que una vista, expandiéndola como si hubieras ejecutado la consulta entera. Resulta entonces que la encapsulación de la SELECT dentro de una función en línea no afecta negativamente el rendimiento. Es posible reescribir la función en forma de una función de valores de tabla de múltiples instrucciones:
CREATE FUNCTION SalesByStore (@storeid varchar(30)) RETURNS @t TABLE (title varchar(80) NOT NULL, qty smallint NOT NULL) AS BEGIN INSERT @t (title, qty) SELECT t.title, s.qty FROM sales s JOIN titles t ON t.title_id = s.title_id WHERE s.stor_id = @storeid RETURN END
Se ejecuta esta función de la misma manera que la función en línea, pero el optimizador de consultas no expande las funciones de múltiples instrucciones. Puedes decir que este tipo de función es como un procedimiento almacenado dentro de la consulta, que devuelve sus resultados en una table temporal.
Si quieres utilizar los resultados de un procedimiento existente en otro, puedes poner la mayor parte del código dentro de una función - en línea o de múltiples instrucciones - y ejecutarla desde el mismo procedimiento. De esta manera, tú puedes servirte de la función, mientras que tus aplicaciones existentes siguen utilizando el procedimiento.
Utilizar funciones de valores de tabla puede dar problemas, ya que el contenido de las funciones es bastante restringido. Puedes hacer un INSERT, UPDATE o DELETE sólo en una variable de tabla dentro la función. No puedes ejecutar procedimientos almacenados - salvo procedimientos extendidos - y no puedes ejecutar SQL dinámico. Además, no puedes emplear funciones de sistema que no tienen la propiedad determinista, es decir, las que no devuelven los mismos resultados con los mismos parámetros - un ejemplo muy típico es getdate() -. Revisa por favor las observaciones en la sección CREATE FUNCTION en Books Online para saber todas las restricciones.
Existe otra restricción que se aplica únicamente a las funciones de valores de tabla de múltiples instrucciones: no puedes utilizar un tipo de datos definido por el usuario en las columnas de la variable de tipo table que se declara para devolver los resultados de la función.
Compartir tablas temporales
Mientras que las functiones definidas por el usuario son una solución nueva y atractiva - pero no apta para todos los entornos - compartir tablas temporales es un método establecido y bien conocido. Las tablas temporales son más incómodas, pero no hay ningunas restricciones, y por lo mucho que detesto la resolución diferida de nombres que existe desde el SQL Server 7, debo conceder que esta característica del TSQL facilita el uso de tablas temporales.
¿Cómo usar este método? No hay nada más sencillo:
CREATE PROCEDURE proc_interior @par1 int, @par2 bit, ... AS ... INSERT/UPDATE/DELETE #tmp go CREATE PROCEDURE proc_exterior AS DECLARE ... CREATE TABLE #tmp (col1 int NOT NULL, col2 char(5) NULL, ...) ... EXEC proc_interior @par1, @par2 ... SELECT * FROM #tmp go
¿No hay de qué asustarse, verdad? El único problema es que si tienes muchos procedimientos que ejecutan proc_interior y quieres cambiar los datos devueltos este procedimiento, pues puede ser necesario cambiar la estructura de la tabla temporal en todos los otros procedimientos que lo ejecutan. Digo puede ser necesario porque tienes la posibilidad de definir proc_interior como "un procedimiento que inserta en las columnas a, b y c de la tabla #tmp, pero la tabla puede tener columnas adicionales que no me importan aquí". Así este método tiene cierta flexibilidad.
A pesar esta flexibilidad, muchas veces quieres que la table temporal tenga la misma estructura en todos los procedimientos. Si la tabla tiene sólo tres columnas, y no la modificas frecuentemente, pues no hay ningún problema. Pero si tiene cincuenta columnas, y la estructura cambia cada semana, pues hay un problema de mantenimiento. Una posible solución sería emplear un pre-processor, y aunque es cierto que el SQL Server no incluye esta raridad, pero si tienes un compilador de C puedes servirte de su pre-processor.(Donde yo trabajo, tenemos nuestro propio pre-processor que se llama Preppis y está incluido en las herramientas AbaPerls, que están disponibles gratuitamente como freeware.) Y a finales de esta sección consideraremos otra solución.
Debo señalar que compartir tablas temporales puede afectar el rendimiento, ya que al ejecutar proc_interior el optimizador lo compilará de nuevo cada vez. Esto puede ser importante o no: depende de la longitud del procedimiento, y cuán frencuentemente se ejecuta. En todo caso, vamos a ver otra manera de usar tablas temporales que no provoca este problema.
El ejemplo anterior no funciona en el SQL Server 6.5. Al crear proc_interior, te sale un error porque la tabla #tmp no existe. Podemos arreglarlo cambiando el código como sigue:
CREATE TABLE #tmp (col1 int NOT NULL, col2 char(5) NULL, ...) go CREATE PROCEDURE proc_interior @par1 int, @par2 bit, ... AS ... INSERT/UPDATE/DELETE #tmp go DROP TABLE #tmp go CREATE PROCEDURE proc_exterior AS DECLARE ... CREATE TABLE #tmp (col1 int NOT NULL, col2 char(5) NULL, ...) ... EXEC proc_interior @par1, @par2 ... SELECT * FROM #tmp go
Ya ves que tienes que crear la table temporal antes de crear el procedimiento, pero debes quitarla antes de crear los otros procedimientos que lo ejecutan. El tema de manejar cambios en la estructura de la tabla temporal se presenta de nuevo, pero como ya he dicho, puedes emplear un pre-processor para ayudarte.
Cabe señalar que el método para la versión 6.5 funciona perfectamente bien con las versiones 7 y 2000, y aun puede ser preferible. Puesto que la tabla temporal existe antes de crear proc_interior, el mismo SQL Server comprueba que tu código no refiere a columnas o tablas que no existen, mientras que cuando utilizas el primer método sólo comprueba la sintaxis, y luego al ejecutar un consulta puede que te salga un error. (Ya he dicho que no me gusta nada la resolución diferida de nombres, ¿verdad?)
Si quieres usar este método con un procedimiento existente, pues utiliza la solución que ya hemos visto para la funciones de valor de tabla: poner la mayor parte del código en un procedimiento central, y ejecutarlo desde el procedimiento actual para que tus aplicaciones sigan funcionando sin problemas.
Ya hemos visto que el mantenimiento y la recompilación son las desventajas de esta solución, pero puedes evitarlas empleando una tabla permanente que incluye spid en su clave principal.
CREATE TABLE cuasi_tmp (spid int NOT NULL, col1 int NOT NULL, col2 char(5) NULL, ...) go CREATE CLUSTERED INDEX cuasi_ix ON cuasi_tmp (spid) -- Puedes añadir más columnas go ... INSERT cuasi_tmp (spid, col1, col2, ....) VALUES (@@spid, @val1, @val2, ...) ... SELECT col1, col2, ... FROM cuasi_tmp WHERE spid = @@spid ... DELETE cuasi_tmp WHERE spid = @@spid
Hay que hacer un poco más trabajo aquí: un proceso que ejecuta el procedimiento debe quitar sus datos de la tabla tras utilizarla - e incluso antes, lo que es más seguro -. Si quieres utilizar la tabla directamente desde una aplicación que emplea conjuntos de resultados desconectados, puede que necesites un GUID u otro identificador en lugar de @@spid - @@spid es el ID de un proceso en el SQL Server -.
Si tienes sólo un procedimiento que ejecuta un otro, es cierto que crear una nueva tabla temporal puede ser una solución excesiva. Pero si muchos procedimientos deben ejecutar un otro, pues merece la pena de considerar est método.
INSERT-EXEC
INSERT-EXEC es una de las dos soluciones que puedes emplear sin cambiar tu procedimiento almacenado:
CREATE TABLE #tmp (...) INSERT #tmp (...) EXEC @err = some_sp @par1, @par2 ...
La estructura de #tmp debe corresponder a la del conjunto de resultados del procedimiento, y el procedimiento puede devolver un solo conjunto de resultados. (Aunque puede funciona si cada conjunto de resultados tiene la misma forma.)
Al primer visto, esta solución parece muy cómoda, pero tienes que considerar los problemas siguientes:
- No puedes anidar procedimientos. Si mi_sp intenta ejecutar mi_otro_sp mediante un INSERT-EXEC, te sale un error ya que sólo una construcción INSERT-EXEC puede estar activa - se trata de una restricción en el SQL Server -.
- Hay problemas de mantenimiento. Si alguien cambia la estructura del conjunto de resultados, el INSERT-EXEC deja de funcionar ya que debe corresponder exactamente a la tabla temporal. (A diferencia del compartir tablas temporales, que no provoca tantos errores.)
- El procedimiento se ejecuta dentro de una transacción. La ejecución del procedimiento forma parte de un INSERT, de ahí que debe haber una transacción y los bloqueos resultantes pueden ser de más larga duración que quieres, lo que puede tener un impacto negativo en el rendimiento y la concurrencia. Es un problema particular en el SQL Server 6.5, ya que la creación de tablas temporales dentro de una transacción puede impedir que otros procesos creen o quiten tablas temporales.
- Un ROLLBACK termina el lote. Si el procedimiento hace un ROLLBACK, te sale un error ya que no se admite el uso de ROLLBACK dentro de INSERT-EXEC. No obstante, la transacción se deshace - ya que el lote se termina -. Una advertencia: en la versión 6.5 el resultado es aún peor; al probarlo, el proceso de SQL Server se cayó y al reconectarme descubrí que la base tempdb, en la que hice mi prueba, fue corrupta.
- INSERT-EXEC no inicia una transacción implícita en ciertas versiones de SQL Server. Sólo tienes que preocuparte de eso si 1) tienes el SQL 7 o el SQL 2000 - hasta el SP3 - y 2) utilizas SET IMPLICIT_TRANSACTIONS ON. Cuando la opción está ON, un INSERT debe iniciar una transacción, pero no lo hace ya que hay un bug en el INSERT-EXEC. Este defecto ha sido corregido en el SQL 2000 SP4 y el SQL 2005; no existe en el SQL 6.5.
A causa de los varios problemas, para mí no es recomendable utilizar INSERT-EXEC como una solución general. No obstante, hay un caso en el que el INSERT-EXEC es la mejor solución: me refiero a cuando no puedes cambiar un procedimiento, por ejemplo los procedimientos extendidos o de sistema - desgraciadamente hay procedimientos de sistema que devuelven múltiples conjuntos de resultados, pues no puedes usar INSERT-EXEC -.
Puedes servirte de INSERT-EXEC con el SQL dinámico también:
INSERT #tmp (...) EXEC(@sql)
Aquí, se supone que la consulta en @sql ha sido creada en el mismo procedimiento o lote, pues no hay ningún problema de mantenimiento, pero no lo puedes anidar, y todavía ejecuta dentro de una transacción. Quizá sea mejor incluir el INSERT en el EXEC(), pero si utilizas esta consulta para devolver un valor escalar para asignarlo a una variable en el procedimiento exterior, probablemente sería mejor emplear sp_executesql. El SQL dinámico es un tema complejo, y si no lo conoces te aconsejo leer mi artículo Las virtudes y maldades del SQL dinámico.
El INSERT-EXEC existe desde la versión 6.5 del SQL Server.
OPENQUERY
Quiero empezar diciendo que por lo general el OPENQUERY es una mala solución, porque hay unas trampas y además problemas de rendimiento. Pero lo incluyo aquí para que este artículo sea total y también porque de vez en cuando es la solución más adecuada.
OPENQUERY - y OPENROWSET - existe desde el SQL Server 7. Permite enviar una consulta a un servidor vinculado, lo que puede ser muy útil ya que admite el uso no sólo de fuentes de datos relacionales, sino también fuentes no relacionales, p.ej. Active Directory. Además, puedes enviar una consulta callback a tu propio servidor:
SELECT * FROM OPENQUERY(LOCALSERVER, 'EXEC sp_who') WHERE status = 'runnable'
De esta manera, puedes utilizar SELECT con el conjunto de resultados de cualquier procedimiento almacenado.
Puede ser una solución ingeniosa, pero hay que preparar el terreno. Primero, las opciones ANSI_NULLS y ANSI_WARNINGS deben estar ON para todas las consultas que utilizan servidores vinculados. En una consulta ad hoc sólo tienes que ejecutar lo siguiente:
SET ANSI_NULLS ON SET ANSI_WARNINGS ON
Pero si ejecutas OPENQUERY dentro de un procedimiento almacenado, debes saber que se fija el valor de la opción ANSI_NULLS al crear el procedimiento. Es decir, debes comprobar que la opción está ON al crear o modificar el procedimiento. (NB: Por defecto, ADO, ODBC y el Analizador de Consultas del SQL Server 2000 desactivan estas opciones.)
Segundo, hay que definir LOCALSERVER. En mi ejemplo parece como una palabra clave, pero en realidad no es nada más que un nombre. Se define este nombre así:
sp_addlinkedserver @server = 'LOCALSERVER', @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = @@servername
Debes ser miembro del grupo sysadmin o setupadmin para crear un servidor vinculado.
OPENQUERY abre una nueva conexión al SQL Server, pues hay que notar lo siguiente:
- El procedimiento ejecutado con OPENQUERY no puede acceder a tablas temporales creadas en la conexión actual.
- La nueva conexión tiene su propia base de datos predeterminada - definida con sp_addlinkedserver, por defecto es la base master - entonces debes especificar la base en la que se encuentra el procedimiento
- Al ejecutar OPENQUERY, si tienes una transacción abierta y has bloqueado algo, el procedimiento almacenado que ejecutas no tiene acceso a los objetos bloqueados. O sea, ten cuidado que no te bloquees a ti mismo.
- Abrir una conexión al sevidor necesita recursos, pues afecta al rendimiento.
Hay dos problemas adicionales que quiero mencionar. Primero, OPENQUERY no admite variables para sus parámetros, pues no puedes ejecutar una consulta como esta:
SELECT * FROM OPENQUERY(LOCALSERVER, @sql)
Si el procedimiento tiene parámetros cuyos valores pueden cambiar para cada ejecución, tienes que utilizar el SQL dinámico, lo que lleva consigo muchos inconvenientes. Aquí hay un ejemplo de OPENQUERY con el SQL dinámico.
Segundo, OPENQUERY utiliza el proveedor SQLOLEDB para conectar al SQL Server, y este proveedor ejecuta sus consultas de una manera posiblemente confusa. Lo que ocurre es que SQLOLEDB ejecuta cada sentencia dos veces: primero con SET FMTONLY ON para tener los metadatos. Cuando la opción SET FMTONLY está en ON, el SQL Server no ejecuta las consultas, sino las examina para generar metdatos. El SQLOLEDB utiliza los metadatos para determinar la estructura del eventual conjunto de resultados, pero si el procedimiento almacenado crea una tabla temporal te sale un error ya la tabla nunca fue creada:
SELECT * FROM OPENQUERY(LOCALSERVER, 'EXEC pubs..sp_fkeys authors')
Aquí está el resultado:
Servidor: mensaje 208, nivel 16, estado 1, línea 1 El nombre de objeto '#fkeysall' no es válido.
Resulta que #fkeysall es una tabla temporal creada dentro de sp_fkeys. Puedes solucionar el problema así:
SELECT * FROM OPENQUERY(LOCALSERVER, 'SET FMTONLY OFF EXEC pubs..sp_fkeys authors')
¡Pero ten cuidado! En este caso, se ejecuta el procedimiento dos veces, lo que seguramente tendrá un impacto en el rendimiento. Y si el procedimiento cambia datos en la base de datos, los datos cambiarán dos veces también, lo que puede ser totalmente incorrecto.
SET FMTONLY puede provocar más confusión:
CREATE TABLE nisse (a int NOT NULL) go CREATE PROCEDURE tonto_sp @x int AS --SET NOCOUNT ON INSERT nisse VALUES (@x) SELECT @x * @@trancount SELECT @x * 3 go SELECT * FROM OPENQUERY(KESÄMETSÄ, 'EXEC tempdb.dbo.silly_sp 7') go SELECT * FROM nisse go
Aquí está el resultado - parcial -:
Could not process object 'EXEC tempdb.dbo.tonto_sp 7'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.
El error sale porque al ejecutar la consulta, el SQLOLEDB interpreta el mensaje filas afectadas provocado por el INSERT como un conjunto de resultados. Por eso debo incluir SET NOCOUNT ON en mi procedimiento.
Antes de enviar la consulta a SQL Server otra vez para la ejecución normal, el SQLOLEDB hace SET IMPLICIT_TRANSACTIONS ON. Cuando esta opción está ON, el SQL Server inicia una transacción al ejecutar un INSERT, UPDATE o DELETE - y otras sentencias también, revisa el Books Online para saber más - . Este comportamiento puede sorprenderte, como el ejemplo anterior ha demostrado. Con SET NOCOUNT ON, aquí está el resultado:
----------- 7 (1 row(s) affected) a ----------- (0 row(s) affected)
Como ves, el procedimiento tonto_sp me devuelve '7', lo que indica que @@TRANCOUNT debe ser 1, pues hay una transacción abierta a pesar de no haber un BEGIN TRANSACTION en el procedimiento. No me devuelve '21' como si hubiera ejecutado tonto_sp directamente, ya que OPENQUERY devuelve un solo conjunto de resultados. Nota también que al hacer un SELECT directamente de nisse tras la ejecución de OPENQUERY, la tabla está vacía porque la transacción implícita ha sido deshecha.
Unas últimas consideraciones para el uso de OPENQUERY:
- Si utilizas variables de valor de tablas en lugar de tablas temporales dentro del procedimiento que ejecuta OPENQUERY pues no tienes que emplear SET FMTONLY OFF. Pero si puedes cambiar tus procedimientos, ya he presentado otras y mejores posibilidades para acceder al conjunto de resultados.
- Puedes configurar el servidor vinculado con MSDASQL en lugar de SQLOLEDB, es decir OLE DB sobre ODBC. Parece que MSDASQL no tiene las mismas peculiaridades, pero Microsoft no aconseja el uso de MSDASQL, pues yo no puedo tampoco.
Puede que te sientas desmayado por la complejidad de OPENQUERY, y en realidad esto refuerza el punto esencial: OPENQUERY no ha sido elaborado para acceder a datos locales, y debes pensarlo bien antes de utilizarlo.
Menciones y contacto
El SQL Server MVP Umachandar Jayachandran me informó sobre el tema de SET FMTONLY ON. El SQL Server MVP Tony Rogerson me señaló el hecho de que la tabla cuasi_tmp debe tener un índice agrupado en la columna spid. Simon Hayes ha propuesto una clarificaciones y ha traducido el artículo en castellano.
Si tienes sugerencias o correcciones para el contenido, lenguaje o formateo de este artículo, por favor envíame un email: esquel@sommarskog.se. Si tienes preguntas técnicas que cualquier persona informada puede contestar, te aconsejo preguntarlas en los newgroups microsoft.public.sqlserver.programming o comp.databases.ms-sqlserver.
Un artículo SQL de Erland Sommarskog, SQL Server MVP.
Traducido por Simon Hayes. Extraido de http://www.hayes.ch/sql/compartir_datos.html
Historia de cambios
2005-04-17 - Ahora disponible en castellano.
2005-03-27 - Pequeñas clarificaciones propuestas por Simon Hayes. El defecto de INSERT-EXEC y SET IMPLICIT_TRANSACTIONS ON ha sido corregido en el SQL 2000 SP4 y el SQL 2005.
No hay comentarios:
Publicar un comentario