jueves, 22 de marzo de 2007

Las virtudes y maldades del SQL dinámico

Un artículo SQL de Erland Sommarskog, SQL Server MVP.

Traducido por Simon Hayes.

Este artículo está disponible también en coreano y alemán,gracias al ASP MVP Jongshin Kim, y a Frank Kalis, respectivamente.

En los varios newsgroups en los que se discute el SQL Server de Microsoft, mucha gente pregunta cómo ejecutar consultas tales como las siguientes:

SELECT * FROM @tabla SELECT @columna FROM tabla SELECT * FROM tabla WHERE x IN (@listado) 

Frecuentemente, alguien responde diciendo utiliza el SQL dinámico y adjunta un ejemplo sencillo, pero no se suele mencionar los inconvenientes de esta solución.

En este artículo analizo el uso del SQL dinámico en procedimientos almacenados en el SQL Server - es una característica potente, pero tienes que utilizarla con cuidado. Empiezo describiendo por qué utilizamos procedimientos almacenados, antes de tratar el uso del SQL dinámico. Examino los conflictos entre los beneficios de los procedimientos almacenados y los efectos del SQL dinámico, incluido el muy conocido problema de seguridad que es la inyección de SQL . Propongo unas buenas prácticas para el código SQL, y termino examinando los casos en que la gente suele utilizar el SQL dinámico, por motivos correctos o incorrectos; en los casos dónde el SQL dinámico no es la solución más adecuada, ofrezco otras posibilidades.

Contenido:

¿Por qué utilizar procedimientos almacenados?
EXEC() y sp_executesql
EXEC()
sp_executesql
Qué método utilizar
Los cursores y el SQL dinámico
El SQL dinámico y los procedimientos almacenados
La inyección de SQL: un problema grave de seguridad
Las buenas prácticas de código y el SQL dinámico
Cuándo utilizar o no el SQL dinámico
select * from @tabla
select * from ventas + @yymm
update tabla set @columna = @valor where keycol = @key
select * from @bbdd + '..tabla'
select * from tabla where columna in (@listado)
select * from tabla where @criterios
Criterios de búsqueda dinámicos
select * from tabla order by @columna
select top @n from tabla order by @columna
create table @tabla
Servidores vinculados
OPENQUERY()
Longitud de columna dinámica
Menciones y contacto
Historia de cambios

¿Por qué utilizar procedimientos almacenados?

Antes de examinar el SQL dinámico, quiero aclarar por qué utilizamos procedimientos almacenados, ya que podríamos desarrollar una aplicación que envía consultas de SQL ad hoc directamente del cliente o de la capa intermedia en lugar de ejecutar procedimientos. No utilizamos los procedimientos almacenados para divertirnos, sino porque llevan varias ventajas.

1. Los permisos

Un procedimiento almacenado es la solución clásica para manejar el acceso de los usuarios a los datos. Un usuario no debería tener los permisos para ejecutar SELECT, INSERT, UPDATE y DELETE directamente - mediante una herramienta como el Query Analyzer podría hacer cualquier cosa. Por ejemplo, una persona podría aumentar su salario en la base de datos del departamento de personal... Ya que un procedimiento almacenado se ejecuta con los permisos de su propietario, el usuario no necesita permisos explícitos en las tablas.

Pero hoy tenemos más posibilidades. Desde el SQL Server 7, puedes dar acceso a una función de aplicación que se activa con una contraseña escondida en la aplicación. Aún más seguro sería un servidor intermedio como COM+, puesto que los usuarios ni tendrían acceso al servidor MSSQL.

Incluso cuando no adoptas una de esas soluciones, los procedimientos siguen siendo importantes para la seguridad.

2. Almacenar los planes de consulta en la caché

Otra ventaja importante de los procedimientos almacenados es el rendimiento. Cuando un procedimiento almacenado se ejecuta por la primera vez, el SQL Server crea un plan de consulta y lo almacena en la caché para poder reutilizarlo en el futuro. El SQL Server quita el plan de la caché cuándo sea demasiado viejo, o cuándo sea necesario crear un nuevo plan - esto puede ocurrir durante la ejecución del mismo procedimiento, pero no describo el proceso en este artículo -.

SQL Server almacena planes de consulta para instrucciones SQL que no están en un procedimiento almacenado, y es capaz de parametrizar la consulta:

    SELECT * FROM pubs..authors WHERE state = 'CA'     go     SELECT * FROM pubs..authors WHERE state = 'WI' 

La segunda consulta utiliza el plan de consulta generado para la consulta anterior, ya que internamente SQL Server lo almacena así:

    SELECT * FROM pubs..authors WHERE state = @1 

El SQL Server no puede parametrizar consultas complejas, y he observado que puede ser que no se encuentre un plan en la caché cuando la única diferencia entre las dos consultas reside en el espacio blanco. Claro está que aunque el SQL Server utiliza parametrización de una manera eficaz, es más probable que vuelva a utilizar el plan de consulta para un procedimiento almacenado.

El uso de la caché es más importante para pequeños procedimientos - o instrucciones - que se ejecutan frecuentemente y rápidamente, ya que consagrar 500ms a la creación de un plan de consultas tendría un impacto notable. Pero si el procedimiento se ejecuta durante veinte minutos, es aceptable dedicar tres segundos a la creación de un plan.

Para quiénes todavía tienen el SQL Server 6.5, cabe destacar que esa versión almacena planes de consulta únicamente para procedimientos almacenados, no para las consultas de SQL ad hoc.

3. Reducir el tráfico en la red

El tráfico en la red también afecta el rendimiento. Digamos que tienes una consulta compleja que alcanza 50 líneas de código, pero cambias sólo un par de valores en la cláusula WHERE para cada ejecución. Poner esta consulta en un procedimiento reduce la cantidad de bytes que se transmite por la red, lo que puede mejorar el rendimiento si hay mucho tráfico.

La diferencia es aún más evidente cuando tienes una serie de instrucciones SELECT/INSERT/UPDATE interrelacionadas. Un procedimiento almacenado te permite utilizar tablas temporales o variables de tabla para procesar todos los datos en el servidor. Si utilizas instrucciones de SQL ad hoc, tienes que enviar todos los datos entre el servidor y el cliente o la capa intermedia. - En realidad, esto no es cierto, ya que puedes crear tablas temporales sin un procedimiento almacenado, pero en este caso puedes tener problemas con el connection pooling y los recordsets -.

4. Utilizar parámetros de salida

Si quieres ejecutar una consulta de SQL ad hoc que devuelve una sola fila, debes utilizar un conjunto de resultados. Un procedimiento almacenado ofrece la posibilidad de utilizar parámetros de salida, lo que resulta ser mucho más rápido. Si ejecutas sólo una consulta, no notarás la diferencia, pero si ejecutas la consulta siguiente 2000 veces, es probable que ganes mucho devolviendo @key como parámetro de salida en lugar de un conjunto de resultados:

    INSERT tbl (...) VALUES (...)     SET @key = @@identity 

5. Establecer bloques de lógico

Ahora, no hablo de seguridad ni de rendimiento, sino de manejar bien tu código. Los procedimientos almacenados permiten evitar la tarea de generar instrucciones SQL en tu código cliente. Pero sería igualmente posible crear funciones en tu aplicación que generan el código SQL según tus parámetros - aunque puede que el código SQL quede escondido en las profundidades de tu código cliente -.

Existe un caso especial: escribes procedimientos almacenados para administradores y DBAs, y por lo cual siempre serán ejecutados desde el Analizador de Consultas. En este caso, no hay otra manera de agrupar tu código en bloques de lógico.

6. Manejar las dependencias

En un sistema complejo que incluye unos centenares de tablas, muchas veces quieres saber dónde se usa una tabla o columna determinada, por ejemplo si quieres cambiar una columna de alguna manera. Si todo el código está en procedimientos almacenados, sólo tienes que buscar en el texto de los procedimientos para encontrar las referencias. O puedes recrear la base de datos sin la columna o tabla, para ver qué efecto tiene el cambio. Existen también la tabla de sistema sysdepends y el procedimiento almacenado de sistema sp_depends que lo utiliza. Pero es difícil conseguir que los datos en sysdepends sean siempre correctos.

Si envías instrucciones SQL directamente desde tu aplicación, el problema es mucho más grande. Debes buscar en una cantidad mayor de código, y si el nombre de la columna tiene un nombre común como status, pues la tarea puede ser casi imposible. Y en este caso la tabla sysdepends no sirve para nada.

EXEC() y sp_executesql

En el SQL Server, el SQL dinámico de ejecuta con EXEC() o sp_executesql.

EXEC()

Utilizar EXEC() es la manera más sencilla de ejecutar SQL dinámico:

    SELECT @tabla = 'ventas' + @año + @mes     EXEC('SELECT * FROM ' + @tabla) 

Puede que este ejemplo te parezca extremadamente básico, pero quiero destacar unos puntos importantes. Primero, son los permisos del usuario actual que están en vigor al ejecutar la instrucción, incluso cuando la instrucción se halla dentro de un procedimiento almacenado. Segundo, EXEC() es similar al EXEC que se utiliza para ejecutar un procedimiento almacenado, pero ejecuta un lote de instrucciones SQL en lugar de un procedimiento almacenado. Sin embargo, en ambos casos el lote SQL - o procedimiento almacenado - se ejecuta en otro alcance del procedimiento que lo ejecutó, pues hay que notar los puntos siguientes:

  • Dentro del lote SQL no tienes acceso a las variables locales o a los parámetros del procedimiento almacenado externo
  • Si usas USE en el lote SQL, esto no afecta al procedimiento almacenado externo
  • Si creas tablas temporales en el lote SQL se quitan al acabar el lote - como si fuera un procedimiento almacenado - pues no las puedes utilizar en el procedimiento almacenado externo. Sin embargo, el lote SQL tiene acceso a las tablas temporales creadas en el procedimiento almacenado externo.
  • Si cambias las opciones SET en el lote SQL, esto no afecta al procedimiento almacenado externo
  • El plan de consultas para el lote SQL no es parte del plan de consultas para el procedimiento almacenado externo. En cuanto a la caché, el lote SQL es igual a una consulta de SQL ad hoc enviada desde el cliente.
  • Si sale un error que termina el lote - por ejemplo un rollback en un desencadenador - se termina el lote, pero también el procedimiento almacenado externo, y el procedimiento almacenado que lo ejecutó, y así...

A diferencia de la ejecución de un procedimiento almacenado, no puedes utilizar parámetros, y tampoco existe un valor del estado de retorno. El valor de @@ERROR depende de la última instrucción en el lote SQL, pues si hay un error en el lote pero luego una instrucción que se ejecuta sin error, @@ERROR tendrá el valor 0.

EXEC() existe desde el SQL Server 6.0.

Nota que EXEC(@sql) no tiene nada que ver con EXEC @sp, lo que ejecuta un procedimiento almacenado cuyo nombre es el valor de @sp.

sp_executesql

sp_executesql existe desde el SQL Server 7, y lleva la ventaja que admite el uso de parámetros de entrada y salida con la cadena de SQL dinámico. El ejemplo siguiente demuestra el uso de un parámetro de salida:

    DECLARE @sql nvarchar(4000),    -- nvarchar(MAX) en SQL 2005.             @col sysname,             @min varchar(20)     SELECT @col = N'au_fname'     SELECT @sql = N'SELECT @min = convert(varchar(20), MIN(' + @col +                   N')) FROM authors'     EXEC sp_executesql @sql, N'@min varchar(20) OUTPUT', @min OUTPUT     SELECT @min 

Ya ves que es mucho más fácil asignar un valor de tu instrucción de SQL dinámico a una variable local con sp_executesql que con EXEC() - lo puedes hacer mediante una instrucción INSERT EXEC(), pero no es una solución muy cómoda -.

El primer parámetro para sp_executesql es una instrucción SQL. El tipo de datos del parámetro es ntext, pues tienes que utilizar una variable de tipo nvarchar - ya que no se admite la declaración de parámetros ntext -. Si tu instrucción es una cadena literal, pon N antes de la comilla simple para indicar una cadena Unicode. La sentencia puede incluir parámetros que empiezan con una arroba - @ - y que no están relacionados con las variables en al alcance actual. La instrucción SQL es como todas las demás, es decir que los parámetros se admiten únicamente donde la sintaxis SQL los permite. O sea, no puedes utilizar parámetros para los nombres de tablas o columnas , pues si los determinas dinámicamente, tienes que incluirlos en la cadena que será ejecutada.

El segundo parámetro para sp_executesql es un listado de declaraciones de variables en la forma habitual, incluyendo parámetros de salida y valores predeterminados - parece que el tema de los parámetros de salida no está en los Books Online del SQL Server -. El listado es de tipo ntexttambién, y debe incluir todas las variables utilizadas en la sentencia SQL.

Los demás parámetros para sp_executesql son los que has declarado en el listado de parámetros, en el mismo orden - o puedes escribir los nombres explícitamente -.

sp_executesql suele ser más útil que EXEC() por varios motivos. Utilizando el sp_executesql no tienes que contar con la autoparametrización de SQL Server, ya que tú suministras los parámetros. Así, es más probable que el SQL Server utilice un plan de consultas que ya existe en la caché - aunque las diferencias en el espacio en blanco todavía pueden impedir esto -. Hablaré de las otras ventajas de sp_executesql en mi análisis de inyección del SQL y las buenas prácticas de código.

Los puntos que destaqué en la sección sobre EXEC() también se aplican en el caso de sp_executesql:

  • El lote SQL está en su propio alcance, y no tienes acceso a las variables en el procedimiento almacenado exterior
  • Los permisos del usuario actual se aplican
  • El uso de USE no afecta al procedimiento almacenado exterior
  • Tablas temporales creadas por el lote SQL no están accesibles desde el procedimiento almacenado exterior
  • El uso de opciones SET afecta únicamente al lote SQL
  • Si el lote ejecutado por sp_executesql se termina debido a un error, el procedimiento almacenado exterior también se termina
  • El valor de @@ERROR indica el estatus de la última sentencia en el lote SQL

Según dice Books Online, el valor del estado de retorno de sp_executesql puede ser 0 - éxito - o 1 - fallo -. Pero parece que en realidad, el valor del estado de retorno es el valor de @@ERROR, por lo menos en el SQL Server 2000.

Consulta Books Online para tener todos los detalles sobre sp_executesql. Mira también el artículo 262499 en el Microsoft Knowledge Base, donde se explica el uso de parámetros OUTPUT.

Qué método utilizar

Si utilizas el SQL dinámico frecuentemente, pues sp_executesql sería mejor porque el SQL Server puede utilizar el plan de consultas en la caché, y también gozas del uso de parámetros. Claro que no lo puedes utilizar si sigues trabajando con la versión el SQL Server 6.5, pero tampoco es posible si tu sentencia SQL es demasiado larga para una variable nvarchar(4000). EXEC() permite concatenar cadenas:

    EXEC(@sql1 + @sql2) 
Al ejecutar sp_executesql puedes utilizar una sola variable, puesto que el TSQL no permite el uso de expresiones como parámetros cuando ejecutas un procedimiento almacenado. Pero si absolutamente necesitas una consulta parametrizado, pues puedes anidar sp_executesql en una cadena para EXEC():
    DECLARE @sql1 nvarchar(4000),             @sql2 nvarchar(4000),             @state char(2)     SELECT @state = 'CA'     SELECT @sql1 = N'SELECT COUNT(*)'     SELECT @sql2 = N'FROM authors WHERE state = @state'     EXEC('EXEC sp_executesql N''' + @sql1 + @sql2 + ''',                              N''@state char(2)'',                              @state = ''' + @state + '''') 

Aun es posible anidarlo cuando tienes parámetros de salida, mediante un INSERT-EXEC:

    CREATE TABLE #result (cnt int NOT NULL)     DECLARE @sql1  nvarchar(4000),             @sql2  nvarchar(4000),             @state char(2),             @mycnt int     SELECT @state = 'CA'     SELECT @sql1 = N'SELECT @cnt = COUNT(*)'     SELECT @sql2 = N'FROM authors WHERE state = @state'     INSERT #result (cnt)         EXEC('DECLARE @cnt int               EXEC sp_executesql N''' + @sql1 + @sql2 + ''',                                  N''@state char(2),                                     @cnt   int OUTPUT'',                                  @state = ''' + @state + ''',                                  @cnt = @cnt OUTPUT               SELECT @cnt')     SELECT @mycnt = cnt FROM #result 

Puede que prefieras evitar este laberinto de comillas utilizando solamente EXEC() - el código puede ser un poco más claro como verás cuando presento la función definida por el usuario quotestring() -.

En el SQL Server 2005, puedes emplear el nuevo tipo de datos nvarchar(max) para la variable @sql, y no tienes que poner sp_executesql dentro de un EXEC().

Los cursores y el SQL dinámico

Normalmente, deberías evitar los cursores, pero la gente suele preguntar cómo utilizar el SQL dinámico con los cursores, por lo cual presento un ejemplo. No es posible decir DECLARE CURSOR EXEC(), pues tienes que incluir la sentencia entera en una cadena para usar el SQL dinámico:

    SELECT @sql = 'DECLARE my_cur CURSOR FOR SELECT col1, col2, col3 FROM ' + @tabla     EXEC sp_executesql @sql 

Nota que aquí no puedes utilizar un cursor local - los cursores locales se quitan al terminar el alcance - pero Anthony Faull me ha señalado que lo puedes hacer mediante una variable de tipo cursor:

    DECLARE @my_cur CURSOR     EXEC sp_executesql           N'SET @my_cur = CURSOR FOR SELECT name FROM dbo.sysobjects; OPEN @my_cur',           N'@my_cur cursor OUTPUT', @my_cur OUTPUT     FETCH NEXT FROM @my_cur 

Se declara una variable de tipo cursor, y ya ves que la puedes manipular como cualquier otro parámetro. Debo confesar que nunca había visto un uso para las variables de tipo cursor, hasta recibir el ejemplo que Anthony Faull me envió muy amablemente.

El SQL dinámico y los procedimientos almacenados

Ahora podemos examinar los motivos por los cuales utilizamos procedimientos almacenados, y el impacto del SQL dinámico. Vamos a servirnos del procedimiento siguiente como ejemplo:

    CREATE PROCEDURE general_select @tblname nvarchar(127),                                     @key     key_type AS -- key_type is char(3)     EXEC('SELECT col1, col2, col3           FROM ' + @tblname + '           WHERE keycol = ''' + @key + '''') 

Pronto verás que este procedimiento no sirve para nada, porque no permite beneficiar de las ventajas de los procedimientos almacenados. Sería igual construir la sentencia SELECT en tu aplicación y enviarla directamente al SQL Server.

1. Los permisos

Si no puedes permitir que los usuarios accedan directamente a las tablas, no puedes utilizar el SQL dinámico y no hay más qué decir. En algunos casos, puede ser que los usuarios tengan los permisos para SELECT, pero no utilices el SQL dinámico para sentencias de INSERT/UPDATE/DELETE, salvo cuándo estás absolutamente seguro de que los permisos no son un problema. Quiero subrayar que me refiero únicamente a las tablas permanentes; no hay nunca cuestiones de permisos al acceder a tablas temporales.

Si utilizas funciones de aplicaciones, o si tienes una capa intermedia como COM+ que no permite que los usuarios accedan directamente a la base de datos, pues probablemente no tienes que preocuparte mucho por los permisos. No obstante, todavía existen problemas de seguridad que debes considerar, como veremos en las sección sobre la inyección de SQL.

Finalmente, si escribes código para los usuarios sysadmin, pues claro está que no habrá problemas de permisos.

2. Almacenar los planes de consulta en la caché

Ya hemos visto que el SQL Server almacena y reutiliza planes de consulta para las consultas de SQL ad hoc y también para los procedimientos almacenados, aunque suele ser más eficaz utilizar procedimientos. En el SQL Server 6.5 estaba claro que el SQL dinámico era más despacio, ya que el SQL Server tenía que compilar cada consulta dinámica, pero en las versiones más recientes la cuestión no es tan clara.

Por ejemplo, al revisar el procedimiento general_select mencionado arriba, se nota que el plan de consulta será almacenado en la caché, y puede ser parametrizado para los valores de @tblname. Pero sería exactamente igual si generaras la consulta dentro de tu aplicación.

A pesar de todo eso, cabe destacar el hecho de que el uso apto del SQL dinámico puede mejorar el rendimiento. Supongamos que tienes una consulta compleja en un procedimiento almacenado muy largo, y el mejor plan de consulta depende mucho de la cantidad y valores de los datos en tus tablas. Puedes convertir esta consulta en SQL dinámico, esperando que el SQL Server no utilice el plan de la caché - si hay en tu consulta una tabla temporal, es muy poco probable que utilice el plan -. También puedes poner la consulta en otro procedimiento separado, pero el código puede ser más claro siendo todo en un solo procedimiento. Como siempre, esto supone que tu estrategia de permisos admite el uso del SQL dinámico.

3. Reducir el tráfico en la red

En las dos secciones anteriores, hemos visto que el SQL dinámico dentro de un procedimiento almacenado no tiene ninguna ventaja sobre una consulta de SQL puro elaborado dentro de una aplicación. Pero desde el punto de vista de la red, es cierto que hay una ventaja: el SQL dinámico en un procedimiento almacenado no tiene ningún impacto en al red. En el caso de general_select, no ganas mucho, ya que el número de bytes en la consulta dinámica es casi igual al número que se necesita para ejecutar el procedimiento.

Pero digamos que tienes una consulta compleja en que seis tablas se están combinando con condiciones complejas, y que una de las tablas se llama algo así como ventas0101, ventas0102 etc., según la demanda del usuario. El diseño de las tablas no es bueno, como pronto veremos, pero supongamos que no lo puedes cambiar por el momento. Si utilizas un procedimiento almacenado, sólo envías un parámetro con el año y mes al servidor, en lugar de enviar la consulta entera. Si se ejecuta la consulta una vez cada hora, no ganas mucho, pero si se ejecuta todos los cinco segundos y si la red no tiene la capacidad adecuada, es muy probable que ganes algo.

4. Utilizar parámetros de salida

Si escribes un procedimiento únicamente para poder utilizar un parámetro de salida, no afectas nada sirviéndote del SQL dinámico. Pero también puedes aprovecharte de parámetros de salida sin escribir tu propio procedimiento ejecutando sp_executesql directamente desde tu aplicación.

5. Establecer bloques de lógico

No puedo añadir mucho sobre este tema, pero quiero señalar que cuando utilizas los procedimientos almacenados, es mejor esconder los detalles de tu base de datos dentro de tus procedimientos, así que se convierten en una capa de abstracción. Haciéndolo así, tener nombres de tablas como parámetros no es una buena idea, salvo en el caso de herramientas para los sysadmins.

6. Manejar las dependencias

El SQL dinámico impide el mantenimiento eficaz de las dependencias, ya que siempre esconde una referencia que luego no existe en sysdepends. La dependencia tampoco se revela al crear una nueva base de datos sin el objeto referenciado. Pero si puedes evitar el uso de tablas o columnas como parámetros, todavía puedes buscar en tu código SQL para saber si hay referencias a una tabla o columna determinada. Por lo tanto, si utilizas el SQL dinámico, intenta siempre referirte a las tablas y columnas en el código de los procedimientos.

La inyección de SQL: un problema grave de seguridad

La inyección de SQL es una técnica que permite que un atacante manipule tu código SQL para ejecutar algo que tú no quieres ejecutar. Te expones a ataques mediante la inyección de SQL cuando pasas algo directamente del cliente a tu código SQL, o sea SQL dinámico en un procedimiento, o sea SQL generado en tu aplicación. La inyección de SQL afecta todas las bases de datos relacionales, no sólo SQL Server.

Consideramos este ejemplo:

    CREATE PROCEDURE search_orders @custname varchar(60) = NULL,                                    @prodname varchar(60) = NULL AS     DECLARE @sql nvarchar(4000)     SELECT @sql = 'SELECT * FROM orders WHERE 1 = 1 '     IF @custname IS NOT NULL         SELECT @sql = @sql + ' AND custname LIKE ''' + @custname + ''''     IF @prodname IS NOT NULL         SELECT @sql = @sql + ' AND prodname LIKE ''' + @prodname + ''''     EXEC(@sql) 

Digamos que los valores para @custname y @prodname vienen directamente de un campo de input, y que un atacante suministra el valor siguiente para @custname:

    ' DROP TABLE orders -- 

La cadena SQL que resulta del input es la siguiente:

    SELECT * FROM orders WHERE 1 = 1  AND custname LIKE '' DROP TABLE orders--' 

¿Has visto el texto rojo? Este método de atacar la base no tendrá éxito en todos los casos. Es poco probable que un usuario normal conectado directamente al SQL Server tenga los permisos necesarios para quitar una tabla. Pero si el usuario está conectado a través de una aplicación web, y si el servidor web tiene permisos más amplios, pues el ataque tendrá éxito. Incluso si el atacante no puede llevar a cabo su primer ataque, todavía puede ejecutar instrucciones SQL que no debería.

La estrategia del atacante es la siguiente. Primero, intenta una comilla simple en el campo de entrada para ver qué pasa. Si el servidor devuelve un error de sintaxis, el atacante sabe que la vulnerabilidad existe, pues intentará descubrir si se necesita algún carácter o palabra clave para terminar la consulta, antes de añadir su propio comando. Finalmente, añade un carácter que indica un comentario, para que el SQL Server no evalúe la cadena entera, así evitando un error de sintaxis. El atacante puede intentar también un punto y coma, que desde el MSSQL 7 es un terminador de instrucción opcional. Si esto devuelve un error, puede haber encontrado algo como el general_select, y si el valor para el parámetro @tblname viene directamente del valor suministrado por el usuario, el atacante puede crear la cadena siguiente:

    some_table WHERE keycol = 'ABC' DELETE orders 

No olvides que existen más puntos de ataque que los obvios campos de input - si tienes parámetros en un URL que sirven como argumentos para procedimientos almacenados, un atacante los utilizará también.

Si imaginas que un atacante necesita no sólo conocimientos técnicos sino también suerte para encontrar una vulnerabilidad de este tipo, recuerda que hay demasiados hackers con demasiado tiempo. La inyección de SQL es un problema grave de seguridad, y debes defenderte contra ella: existen dos defensas.

  • Da sólo los permisos más mínimos a los usuarios en el SQL Server. Si tu aplicación conecta desde una capa intermedia, utiliza un login normal, que tiene los permisos de SELECT como máximo. Así evitas que un desarrollador descuidado o inexperto cree una vulnerabilidad a la inyección de SQL.
  • Hay prácticas de código muy básicas que puedes utilizar, y las veremos en la próxima sección del artículo.

Insisto en el hecho de que la inyección de SQL afecta no sólo los procedimientos almacenados, sino también el código en aplicaciones donde generas consultas, lo que puede ser aún más vulnerable - ya que muchas veces tus variables de cadena no tienen una longitud fija -. Incluso si utilizas procedimientos almacenados, puede que generes las sentencias EXEC para ejecutarlos, y estas sentencias también son vulnerables.

Las buenas prácticas de código y el SQL dinámico

Escribir código para ejecutar el SQL dinámico puede parecer fácil, pero en realidad debes tener cierta disciplina para no perder el control del código. Si no tienes cuidado, tu código acaba siendo complicado, así que es muy difícil leerlo, detectar problemas, y mantenerlo. Miramos de nuevo el procedimiento almacenado horroroso que es general_select:

    CREATE PROCEDURE general_select @tblname nvarchar(127),                                     @key     key_type AS -- key_type is char(3)     EXEC('SELECT col1, col2, col3           FROM ' + @tblname + '           WHERE keycol = ''' + @key + '''') 

Quizás has visto las múltiples comillas simples, y te has preguntado ¿para qué sirve todo eso? El TSQL es uno de los lenguajes en los que tienes que duplicar un terminador de cadena para que sea evaluado como una cadena normal. Las cuatro comillas consecutivas ('''') son una cadena cuyo valor es una sola comilla ('). Y esto es un ejemplo sencillo - el asunto puede ser aún peor.

Un error muy frecuente es el siguiente:

    EXEC('SELECT col1, col2, col3           FROM' + @tblname + '           WHERE keycol = ''' + @key + '''') 

¿Has visto que falta un espacio tras la palabra FROM? Al crear el procedimiento no te saldrá ningún error, pero al ejecutarlo el SQL Server te dice que no existen las columnas keycol, col1, col2 y col3. Puesto que sabes que sí existen en la tabla que indicaste, te quedarás muy desconcertado. Pero mira el código que se genera, utilizando los parámetros foo y abc:

    SELECT col1, col2, col3 FROMfoo WHERE keycol = 'abc' 

La sintaxis es correcta, porque FROMfoo puede ser un alias para col3.

Ya he dicho que no deberías nunca utilizar nombres de tablas o columnas como parámetros, pero ya que estoy hablando de las buenas prácticas, te lo repito. Cuando empiezas escribir los procedimientos almacenados, deben ser el único código en que te refieres a objetos en la base de datos - salvo cuando te refieres a los procedimientos si mismos, claro -. No obstante, aquí te presento una versión revisada de general_select que permite demostrar unas buenas prácticas para utilizar el SQL dinámico:

    CREATE PROCEDURE general_select @tblname nvarchar(127),                                     @key key_type,                                     @debug bit = 0 AS     DECLARE @sql nvarchar(4000)     SET @sql = 'SELECT col1, col2, col3                 FROM ' + quotename(@tblname) + '                 WHERE keycol = @key'     IF @debug = 1 PRINT @sql     EXEC sp_executesql @sql, N'@key key_type', @key = @key 

Como ya ves, he hecho varios cambios:

  • He puesto @tblname dentro de la función quotename() para protegerme contra un ataque de inyección de SQL mediante esta variable. Hay más detalles sobre quotename() debajo.
  • He añadido un parámetro @debug, pues si me sale un error extraño es fácil comprobar la cadena que se está ejecutando.
  • En lugar de meter el valor de @key directamente en una cadena con EXEC(), me sirvo de sp_executesql así que @key es un parámetro. Es otra manera de protegerme contra la inyección de SQL.

quotename() es una función del sistema que existe desde el SQL Server 7. Devuelve el valor de entrada delimitado por el terminador de cadena que seleccionas y duplica terminadores dentro de la cadena. Por omisión, el terminador es un corchete, ya que la función se usa principalmente con los nombres de objetos, pero puedes seleccionar una comilla simple. Pues si prefieres usar EXEC() por motivo alguno, puedes utilizar quotename() para protegerte contra la inyección de SQL. Vamos a mirar un ejemplo revisado tirado del procedimiento search_orders que hemos visto en la discusión sobre la inyección de SQL:

    IF @custname IS NOT NULL         SELECT @sql = @sql + ' AND custname LIKE ' + quotename(@custname, '''') 

Nota que el parámetro de entrada para quotename() es nvarchar(128), pues no sirve para cadenas largas. Si tienes el SQL Server 2000, puedes utilizar esta función definida por el usuario en su lugar:

     CREATE FUNCTION quotestring(@str nvarchar(1998)) RETURNS nvarchar(4000) AS     BEGIN         DECLARE @ret nvarchar(4000),         @sq  char(1)         SELECT @sq = ''''         SELECT @ret = replace(@str, @sq, @sq + @sq)         RETURN(@sq + @ret + @sq)     END 

- En el SQL Server 2005, reemplaza 1998 y 4000 con MAX -.

La utilizas así:

    IF @custname IS NOT NULL         SELECT @sql = @sql + ' AND custname LIKE ' + dbo.quotestring(@custname) 

En el SQL Server 7, tienes que reescribir quotestring en forma de un procedimiento almacenado. El SQL Server 6.5 no tiene la función replace(), pues no puedes hacer mucho en este caso. - Quiero señalar que fue el SQL Server MVP Steve Kass que me propuso la idea de utilizar quotename() o una función definida por el usuario -.

Otra posibilidad para evitar un laberinto de comillas anidadas es aprovecharte del hecho de que el T-SQL utiliza dos caracteres para delimitar cadenas. Más precisamente, si la opción QUOTED_IDENTIFIER está OFF, también puedes usar una comilla doble ("). El valor predeterminado de esa opción depende de la situación, pero es mejor que esté ON, y debe estar ON para utilizar vistas indizadas e índices en columnas computadas. No es una alternativa perfecta, entonces, pero si eres consciente de estas advertencias, puedes hacer esto:

    CREATE PROCEDURE general_select @tblname nvarchar(127),                                     @key     key_type,                                     @debug   bit = 0 AS     DECLARE @sql nvarchar(4000)      SET @sql = 'SET QUOTED_IDENTIFIER OFF                 SELECT col1, col2, col3                 FROM ' + @tblname + '                 WHERE keycol = "' + @key + '"'     IF @debug = 1 PRINT @sql     EXEC(@sql) 

Resulta ser más fácil leer el código cuando hay dos caracteres que delimitan las cadenas - la comilla simple para el comando SQL y la comilla doble para los valores.

Sería preferible utilizar sp_executesql y quotename() para protegerte contra la inyección de SQL, pero puede una solución adecuada para tareas de sysadmin - ya que la inyección de SQL no sería un problema - y puede ser la mejor solución para el SQL Server 6.5.

Quiero concluir esta sección diciendo que seguir las prácticas que he identificado supone aumentar considerablemente la complejidad de tu código SQL, así que deberías pensar dos veces antes de utilizar el SQL dinámico.

Cuándo utilizar o no el SQL dinámico

Mientras leyendo los newsgroups dedicados al SQL Server, se ve casi todos los días alguien que responde a una pregunta diciendo utiliza el SQL dinámico sin mencionar el impacto en los permisos y el uso de la caché. Aunque muchas de estas preguntas parecen no tener otra solución - si se entiende bien la pregunta - muchas veces existe un problema de lógico o diseño que tiene una mejor solución que es absolutamente diferente.

En esta sección, examino unos casos donde podrías servirte del SQL dinámico - en algunos, puede ser la solución más adecuada, pero en otros es una mala solución, y en otros casos la pregunta viene de un concepto básico completamente equivocado.

select * from @tabla

Se pregunta muy frecuentemente por qué lo siguiente no funciona:

    CREATE PROCEDURE my_proc @tablename sysname AS         SELECT * FROM @tablename 

Ya hemos visto que se puede escribir un procedimiento almacenado para hacer esto, pero también hemos visto que ese procedimiento no tendría sentido. Si esto te parece la buena manera de programar el SQL, pues olvida completamente los procedimientos almacenados.

Parece que la gente quiere hacer esto por varios motivos. Hay personas poco experimentadas en la programación SQL que tiene mucha experiencia en otros lenguajes como C++, VB etc. En aquellos lenguajes, la parametrización es una buena idea, ya que tener código muy genérico para facilitar el mantenimiento es una virtud.

Pero resulta que cuando hablamos de objetos en una base de datos, este principio no se aplica - debes considerar cada tabla y columna como un objeto único y constante. ¿Por qué? Pues porque al crear un plan de consultas, cada tabla tiene sus propias estadísticas y relaciones, y el SQL Server debe tratarlas individualmente. Además, si tienes un modelo complejo, es importante saber qué tablas y columnas están en uso. Sin lugar a dudas, al empezar utilizar nombres de tablas y columnas como parámetros, es el momento en que pierdes el control.

Pues si quieres escribir algo como el procedimiento sobredicho para no teclear tanto, has malentendido. Es mucho mejor escribir diez o veinte procedimientos almacenados, aunque sean muy similares.

No obstante, si tienes muchas consultas complejas, puede que ganes mucho tiempo manteniendo un sólo procedimiento en lugar de varios. En este caso, puedes considerar el uso de un pre-processor como en C/C++: el código reside en un include file, que luego se utiliza para crear múltiples procedimientos almacenados para las varias tablas.

select * from ventas + @yymm

Este caso es algo similar al caso anterior, pero mientras que antes se suponía que existía una cantiadad fija de tablas, parece que hay gente cuyos sistemas crean tablas dinámicamente, p.ej. cada mes una nueva tabla para los datos de ventas. Tener un procedimiento para cada tabla no es una solución práctica, ni siquiera con la ayuda de un pre-processor.

¿No hay otra solución que el SQL dinámico? Pues tenemos que distanciarnos un poco para ver que el concepto básico es incorrecto, ya que el modelo es defectuoso. En sistemas basados en Access o en archivos de datos muy simples, puede ser necesario tener una tabla para cada mes, pero en SQL Server u otro sistema de base de datos de alto rendimiento, no suele ser necesario. El SQL Server y sus competidores fueron diseñados para manejar grandes cantidades de datos, y para acceder a los data mediante las columnas claves. Es decir, el año y el mes son parte de la restricción PRIMARY KEY en una sola tabla denominada ventas.

Si tienes un sistema de legado, diseñar de nuevo la base de datos puede costar mucho - pero mantener el código más complejo basado en el SQL dinámico también to costará -. Si estás desarrollando un nuevo sistema, ni pienses en crear tablas dinámicamente: será muy difícil mantener el código para utilizar las tablas, y si creas las tablas muy frecuentemente - por ejemplo una tabla para cada carrito en una tienda web - corres el riesgo de crear un punto de contención en las tablas del sistema, lo que perjudica el rendimiento.

Quizá no estés convencido, y estás pensando pero tengo millones de filas, la base no funcionará si están todas en la misma tabla. Bueno, si tienes muchas filas pues sí tienes que preocuparte, pero no hablo de unos millones de filas, lo que representa una tarea básica para el SQL Server, siempre que has bien escogido tus índices. Pero si tienes unos centenares de millones de filas, puede que tengas que considerar otras opciones: el SQL Server 2000 ofrece posibilidades como vistas dividas e incluso vistas dividas distribuidas que te permiten dividir tus datos en varias tablas pero seguir accediéndoles como si estuvieran en una sola tabla. (Nota que aunque he hablado del número de filas, para simplificar el tema, pero lo que realmente importa es el tamaño total de la tabla, lo que depende de la longitud de las filas.)

update tabla set @columna = @valor where keycol = @key

Aquí, quieres cambiar los datos en una columna que seleccionas en el momento de la ejecución. La sintaxis de la sentencia arriba es correcta en T-SQL, pero lo que ocurre es que el valor de @valor se asigna a @colname una vez para cada fila afectada en la tabla.

En este caso, utilizar el SQL dinámico supone que el usuario tiene los permisos de UPDATE en la tabla, lo que se suele evitar, pues sería mejor no utilizarlo. Aquí hay otra solución que no es nada complicado:

    UPDATE tbl     SET    col1 = CASE @colname WHEN 'col1' THEN @value ELSE col1 END,            col2 = CASE @colname WHEN 'col2' THEN @value ELSE col2 END,            ... 

Si no conoces la expresión CASE, por favor consulta los Books Online. Es una característica muy potente del lenguaje SQL.

Pero hay que preguntarse otra vez por qué la gente quiere hacer esto. Quizá sus tablas sean algo como éste:

    CREATE TABLE products   (prodid   prodid_type NOT NULL,                             prodname name_type   NOT NULL,                             ...                             sales_1  money       NULL,                             sales_2  money       NULL,                             ...                             sales_12 money       NULL,                             PRIMARY KEY (prodid)) 

Puede ser mejor crear una segunda tabla para las columnas sales_n:

    CREATE TABLE product_sales (prodid prodid_type NOT NULL,                                 month  tinyint     NOT NULL,                                 sales  money       NOT NULL,                                 PRIMARY KEY (prodid, month)) 

select * from @bbdd + '..tabla'

En este caso la tabla reside en otra base de datos cuyo nombre se determina dinámicamente. Por lo visto, la gente tiene varios motivos para querer hacer esto, y cada motivo tiene una solución distinta.

Acceder a datos en otra base de datos. Si por algún motivo tu aplicación necesita dos bases de datos, deberías evitar fijar los nombres en tu código, ya que tendrás un problema si quieres crear un entorno de prueba en el mismo servidor. Se suele pensar a sacar el nombre de la otra base de una tabla de configuración, y generar una consulta dinámicamente. Pero hay otra solución, si puedes poner tu código en un procedimiento en la otra base de datos:

    SET @sp = @dbname + '..some_sp'     EXEC @ret = @sp @par1, @par2... 

Es decir, la @sp contiene el nombre del procedimiento almacenado.

Hacer algo en cada base de datos. Esto suena a una tarea de sysadmin, pues el SQL dinámico puede ser una buena solución ya que no hay que considerar ni los permisos ni la caché. No obstante, existe otra posibilidad, que es usar sp_MSforeachdb:

    sp_MSforeachdb 'SELECT ''?'', COUNT(*) FROM sysobjects' 

Como puedes imaginar, sp_MSforeachdb utiliza el SQL dinámico internamente, así que la ventaja es que no tienes que escribir un bucle tú mismo, pero debo señalar que sp_MSforeachdb no está documentado en Books Online, y entonces Microsoft no lo apoya.

Una base de datos "master". De vez en cuando he encontrado gente que tiene muchas bases de datos con la misma estructura: me imagino que tienen una empresa de hosting y cada base de datos sería para un cliente distinto. Las normas de su empresa no permiten que todos los clientes compartan una sola base de datos. Estas personas quieren evitar la tarea de mantener todas las bases, pues imaginan una sola base "master" donde pueden colocar sus procedimientos almacenados. Pero en este caso, todos los procedimientos almacenados utilizarían el SQL dinámico, que también sería una pesadilla.

Hay dos alternativas. Puedes servirte de la propia base de datos master del SQL Server, e instalar los procedimientos de tu aplicación como procedimientos del sistema. Pero Microsoft no apoya esto, y hay problemas de seguridad, pues no te lo aconsejo.

La otra solución sería crear los procedimientos en cada base de datos, y desarrollar un sistema de manejar y distribuir tus objetos SQL. Tienes que hacerlo en todo caso, para poder modificar las tablas, y si los procedimientos están en todas las bases de datos, puedes proporcionar versiones nuevas a los clientes que las quieren, sin afectar a los clientes que prefieren cambiar más lentamente, por cautela o por mezquines. Cómo desarrollar un tal sistema es cuestión de la gestión de configuración, un tema importante que no puedo analizar en este artículo. Pero te ofrezco dos consejos: el SQL Server Resource Kit incluye una herramienta para crear objetos SQL directamente del Visual SourceSafe. Y yo puedo ofrecerte AbaPerls, unas herramientas que he desarrollado para satisfacer las necesidades de mi propio entorno: es gratis - freeware - y está disponible aquí http://www.abaris.se/abaperls/.

select * from tabla where columna in (@listado)

Una pregunta muy frecuente, y la respuesta utiliza el SQL dinámico es demasiado comuna. El SQL dinámico es una mala solución: no deberías necesitar permisos SELECT para hacer esto, y si la lista tiene muchos elementos, el rendimiento será fatal con el SQL dinámico.

¿Qué es la solución? Conviertes la cadena en filas en una tabla con una función definida por el usuario o un procedimiento almacenado. Aquí no doy ningún ejemplo, ya que he escrito otro artículo en el que analizo muchos métodos para solucionar este problema, e incluyo los datos de rendimiento para cada uno. ¡El SQL dinámico llega último! Es un artículo muy largo, pero hay vínculos a la solución más adecuada para cada versión de SQL Server: Arrays and Lists in SQL Server.

select * from tabla where @criterios

Si piensas escribir un procedimiento almacenado como éste, olvídalo:

    CREATE PROCEDURE search_sp @condition varchar(8000) AS         SELECT * FROM tbl WHERE @condition 

Si haces esto, todavía estás generando las consultas SQL en tu aplicación en lugar de adoptar los procedimientos almacenados. Ve también la próxima sección.

Criterios de búsqueda dinámicos

Muy frecuentemente, un usuario debe poder ejecutar una consulta seleccionando varios criterios distintos. Cualquier desarrollador se da cuenta de que escribir una consulta estática para cada combinación de parámetros es imposible, y la mayor parte de los desarrolladores creen que poner todas las posibles condiciones en una sola consulta compleja no proporcionará un buen rendimiento.

Aquí, sí, el SQL dinámico debe ser la mejor solución. Siempre que los permisos no sean un problema, utiliza el SQL dinámico, que es lo mejor para el rendimiento y el mantenimiento. He escrito otro artículo - Dynamic Search Conditions - en el que tengo ejemplos de escribir este tipo de consulta mediante el SQL dinámico y estático.

select * from tabla order by @columna

Es fácil evitar el SQL dinámico así:

    SELECT col1, col2, col3     FROM   tbl     ORDER  BY CASE @col1                 WHEN 'col1' THEN col1                 WHEN 'col2' THEN col2                 WHEN 'col3' THEN col3               END 

Como he dicho antes, si no conoces la expresión CASE, consulta los Books Online.

Nota que si las columnas tienen tipos de datos distintos, no puedes utilizarlas juntas en la misma expresión CASE, ya que una expresión CASE tiene sólo un tipo de datos. Pero puedes solucionar este problema así:

    SELECT col1, col2, col3     FROM   tbl     ORDER  BY CASE @col1 WHEN 'col1' THEN col1 ELSE NULL END,               CASE @col1 WHEN 'col2' THEN col2 ELSE NULL END,               CASE @col1 WHEN 'col3' THEN col3 ELSE NULL END 

El SQL Server MVP Itzik Ben-Gan ha escrito un buen artículo sobre este tema en el ejemplar de marzo 2001 de SQL Server Magazine, en el que propone otras soluciones.

select top @n from tabla order by @columna

Una solución básica sin utilizar el SQL dinámico:

    CREATE PROCEDURE get_first_n @var int WITH RECOMPILE AS     SET ROWCOUNT @var     SELECT *     FROM   authors     ORDER  BY au_id     SET ROWCOUNT 0 

Quizás hayas oído que el SQL Server no hace caso de SET ROWCOUNT al crear un plan de consultas, y es cierto en el SQL Server 6.5 - en todo caso, en esa versión no tienes TOP, pues no hay otra solución - pero sí lo hace en el SQL Server 7 y 2000. Pero tienes que utilizar un parámetro - no una variable local - con SET ROWCOUNT o el optimizador no sabrá el valor y puede decidir escánear la tabla.

No olvides poner SET ROWCOUNT 0 tras la instrucción SELECT para que las instrucciones siguientes no sean afectadas.

Books Online tiene unas advertencias sobre el use de SET ROWCOUNT, y aconseja no utilizar SET ROWCOUNT con instrucciones DELETE, INSERT y UPDATE No sé precisamente por qué, pero creo que un INSERT en una tabla temporal con SET ROWCOUNT en vigor está bien. Un INSERT un una tabla que tiene desencadenadores puede sorprenderte, ya que el SET ROWCOUNT afecta el desencadenador también.

Considera también por qué quieres hacerlo. Si quieres limitar los datos que se devuelven a una página de Internet, puede ser mejor leer cada vez 500 filas, para no volver siempre a la base de datos cuando el usuario hace clic en "Próximo". - Y a mí me dan asco las páginas que limitan los datos que puedo ver a sólo diez o veinte líneas -.

create table @tabla

En este caso, no hay problemas ni de seguridad ni de la caché - no hay un problema de permisos, puesto que el usuario necesita los permisos de CREATE TABLE incluso cuando el comando está estático y en un procedimiento almacenado -. Y además, tampoco hay problemas con las dependencias, pues al parecer no existen razones contra el SQL dinámico.

Pero sí hay una pregunta importante: ¿Por qué? ¿Qué motivo puedes tener? Quizá sea razonable en un script de sysadmin si necesitas crear un par de tablas similares, pero si vas creando tablas dinámicamente en tus aplicaciones pues te has saltado todas las reglas de diseño de los bases de datos. En una base de datos relacional, el conjunto de tablas y columnas debería ser fijo. Puede cambiar al instalar una nueva versión, pero no durante la ejecución de tu aplicación. Hay más detalles en esta sección: select * from ventas + @yymm

A veces parece que la gente quiere crear nombres únicos para tablas temporales, pero es absolutamente innecesario puesto que el SQL Server ya tiene esta característica. Puedes crear una tabla temporal con la sintaxis siguiente:

    CREATE TABLE #nisse (a int NOT NULL) 

El nombre real de la tabla será más largo, y sólo la conexión actual puede ver esta instancia de #nisse.

Si quieres crear una tabla permanente para cada conexión - quizás utilices conjuntos de resultados desconectados y entonces no puedes utilizar las tablas temporales -, puede ser mejor crear una sola tabla para todos las conexiones, cuya primera columna es una clave única generado para cada conexión.

Servidores vinculados

Este caso parece similar a parametrizar el nombre de una base de datos, pero las respuestas no son exactamente las mismas. Si puedes crear un procedimiento almacenado en el servidor vinculado, puedes generar el nombre del procedimiento dinámicamente:

    SET @sp = @server + 'db.dbo.some_sp'     EXEC @ret = @sp @par1, @par2... 

Si quieres hacer una combinación de una tabla local y una tabla en un servidor remoto que no puedes identificar de antemano, el SQL dinámico sería la mejor solución.

Pero hay una alternativa, aunque no es útil en todos los casos. Puedes utilizar sp_addlinkedserver para crear un alias temporal:

    EXEC sp_addlinkedserver MYSRV, @srvproduct='Any',                                    @provider='SQLOLEDB', @datasrc=@@SERVERNAME     go     CREATE PROCEDURE linksrv_demo_inner WITH RECOMPILE AS         SELECT * FROM MYSRV.master.dbo.sysdatabases     go     EXEC sp_dropserver MYSRV     go     CREATE PROCEDURE linksrv_demo @server sysname AS     IF EXISTS (SELECT * FROM master..sysservers WHERE srvname = 'MYSRV')         EXEC sp_dropserver MYSRV     EXEC sp_addlinkedserver MYSRV, @srvproduct='Any',                                    @provider='SQLOLEDB', @datasrc=@server     EXEC linksrv_demo_inner     EXEC sp_dropserver MYSRV     go     EXEC linksrv_demo 'Server1'     EXEC linksrv_demo 'Server2' 

Hay dos procedimientos almacenados. El procedimiento externo crea el servidor vinculado MYSRV para acceder al servidor remoto que queremos utilizar en este momento, y lo quita una vez terminada la consulta. El procedimiento interno ejecuta la consulta, y he incluido WITH RECOMPILE para que SQL Server no vuelva a utilizar un plan de consultas viejo creado para otro servidor.

Esta solución sólo funciona en estas circunstancias:

  • La persona que ejecuta el procedimiento debe tener los permisos para crear servidores vinculados - por omisión sysadmin y setupadmin tienen esos permisos - pues no sirve para los usuarios normales.
  • Ya que los servidores vinculados se definen globalmente al nivel del servidor, varias personas no pueden ejecutar el procedimiento a la vez - claro está que deberías utilizar el alias MYSRV solamente dentro de este procedimiento -.

Nota: si lo pruebas, es probable que funcione sin WITH RECOMPILE.. Puedes conseguir que funcione con la ejecución de sp_addlinkedserver en el mismo procedimiento que la ejecución de la consulta que utiliza el servidor vinculado, pero si éste no existe cuando SQL Server necesita crear un plan de consultas para el procedimiento pues no funcionará.

OPENQUERY

Muchas veces, el uso de las funciones de conjunto de filas OPENQUERY y OPENROWSET supone el uso del SQL dinámico, ya que su segundo argumento es una cadena de SQL y no admiten los variables. Resulta ser un poco incómodo, ya que puedes necesitar tres niveles anidados de comillas, pues la función quotestring() que ya hemos visto es muy útil:

DECLARE @remotesql nvarchar(4000),         @localsql  nvarchar(4000),         @state     char(2)  SELECT @state = 'CA' SELECT @remotesql = 'SELECT * FROM pubs.dbo.authors WHERE state = ' +                     dbo.quotestring(@state) SELECT @localsql  = 'SELECT * FROM OPENQUERY(MYSRV, ' +                     dbo.quotestring(@remotesql) + ')',  PRINT @localsql EXEC (@localsql) 

La función del sistema quotename() no es tan útil, puesto que la instrucción SQL sobrepasa frecuentemente el límite de 129 caracteres para el parámetro de entrada de quotename().

Longitud de columna dinámica

Digamos que tienes un procedimiento almacenado que devuelve unos datos, y se ejecutará directamente en el Query Analyzer - probablemente esun procedimiento para los sysadmins -. Para formatear los resultados, quieres cambiar la longitud de las columnas para que no haya ningunos espacios en blanco a finales de los resultados, pero tampoco quieres truncar los datos. Puedes realizar esta tarea con el SQL dinámico, y ya que se suele utilizar una tabla temporal, no hay problemas de permisos.

No doy un ejemplo, pero te aconsejo mirar código existente, en el procedimiento almacenado popular - pero no documentado - sp_who2. Puedes visualizar el código ejecutando exec master..sp_helptext sp_who2, o con el Object Browser en Query Analyzer o Enterprise Manager.

Hay otro ejemplo en mi website, en el procedimiento aba_lockinfo.

Menciones y contacto

Quisiera agradecerles a las personas siguientes que me han proporcionado valiosas sugerencias e ideas para este artículo: los SQL Server MVPs Tibor Karaszi, Keith Kratochvil, Steve Kass, Umachandar Jaychandran y Hal Berenson, además de Pankul Verma, Anthony Faull, Marcus Hansfeldt, Jeremy Lubich y Simon Hayes.

Quiero también agradecerles al ASP MVP Jongshin Kim su traducción coreana del artículo, a Frank Kalis su traducción alemán, y a Simon Hayes su traducción español.

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.

Historia de cambios

2005-04-17 - Nuevo ejemplo de EXEC y sp_executesql con un parámetro de salida. Añadí el uso de nvarchar(max) en el SQL 2005 p.ej. para quotestring.

2004-05-30 - Ahora disponible en castellano.

2004-02-08 – Ahora disponible en alemán. Pequeñas correcciones.

2003-12-02 – Ahora disponible en coreano. Añadí un ejemplo de utilizar una variable cursor con el SQL dinámico. Modifiqué la descripción del primer parámetro de sp_executesql.

Vuelve al home page de Erland

Compartir datos entre procedimientos almacenados

En los newsgroups de SQL Server, se leen frecuentemente las preguntas siguientes: ¿Cómo puedo utilizar los resulatados de un procedimiento almacenado en otro? o ¿Cómo puedo utilizar el conjunto de resultados de un procedimiento almacenado en una consulta SELECT?

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:

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_id 
El 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 TRANSACTION 
La 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.

Vuelve al home page de Erland.

lunes, 19 de marzo de 2007

Tutorial para consultar una base de datos desde excel, pasando parametros

Tutorial para consultar una base de datos desde excel

Cuando hacia consultas a BBDD desde excel, una de las principales necesidades, por lo menos para mi fue pasarle por parámetro el valor de una celda a Microsoft Query, esto a partir de las ultimas versiones del paquete Microsoft Oficce ya esta solucionado y voy a tratar de explicarles rapidamente como se hace.

Les recomiendo que si no tienen conocimientos de administración de origenes de datos como ODBC, primero se informen sobre esto, no es complicado pero si indispensable para conectar Excel con Microsoft Query.

Bueno a la receta:

Abrimos una hoja nueva de Excel y nos vamos a Datos -> Obtener Datos Externos -> Nueva consulta de Base de Datos. En este paso puede pasar que necesitemos el CD de Oficce por que no tengamos instalados los componentes de Microsoft Query

Elegir un origen de datos

Si lo tenemos lo seleccionamos o podemos crear uno nuevo haciendo clic en <nuevo origen de datos> para crear uno nuevo le damos aceptar y nos pide:

- El nombre, por ejemplo Productos dado que vamos a consultar la tabla productos de la tabla de la BBDD Northwind que trae de ejemplo SQL server

- El proveedor de datos, en esta caso es Sql Server

- Conectar, ponemos el servidor y los datos de autenticación, IMPORTANTE ver las opciones para seleccionar la Base de Datos a la que queremos entrar ya que por defecto es la master y no hay tablas de usuario ahí (o no debería haber, si las tienes preocúpate ;)). Seleccionamos Northwind para nuestro ejemplo

Aparece el asistente para consultas, buscamos la tabla Products y seleccionamos todos los campos, seguimos con siguiente todas las pantallas hasta la ultima, y aca ponemos Ver los datos o modificar la consulta en Microsoft Query

Aquí esta el primer secreto, primero vemos que no este seleccionada la opcion Consulta Automatica que por defecto si lo esta.

Ademas hay que seleccionar el boton de Ocultar o mostrar criterios

Elegimos el campo SupplierID (proveedor) en Campos de Criterio en valor, esta el segundo secretito, poner alguna leyenda entre corchetes como por ejemplo [Escriba el Codigo de Proveedor]

Aqui los que conozcan algo de SQL se pueden hacer sus propias consultas, tan complejas como lo necesiten

Elegir Archivo -> Devolver los datos a Excel

Ahora le decimos que queremos situar los datos en $A$2, seleccionamos parámetros, elegimos la celda de donde tomara los datos, en este caso sera =Hoja1!$A$1, seleccionamos Actualizar automáticamente cuando cambie el valor, Aceptamos y listo

En principio no veremos ningun registro, pero prueben poniendo en la celda A1 un 2 por ejemplo y aparecen todos los productos del proveedor 2. Por si quedaron dudas, les dejo un video explicativo de mi autoria. Saluds