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.
1 comentario:
Simplemente Gracias.
Publicar un comentario