SQL Dinámico con retorno de valores en procedimientos de SQL Server

No replies
Imagen de carlos
Offline
Joined: 28/12/2005
Puntos: 1582

Si necesitas parametrizar algo más que los valores de los campos que se introducen en la parte del WHERE en una sentencia T-SQL de un procedimiento almacenado seguramente vas a tener que utilizar SQL dinámico, aunque si se puede hacer lo mismo sin SQL dinámico siempre es mejor, más seguro y más eficiente.

Ejemplo de sentencias que se pueden hacer dentro de un procedure con SQL 'Estático'

INSERT INTO estadisticas(idFecha,fecha,bd,tabla,campo,filtro,funcion,valor) 
            values (@idfecha,getdate(),@bd,@tabla,@campo,'','COUNT',@valor)

SELECT @valor= valor FROM estadisticas WHERE bd=@bd and tabla=@tabla and campo=@campo

Ejemplos de sentencias que se pueden hacer dentro de un procedure con SQL dinámico

Si es necesario parametrizar el nombre de la tabla de la query, o partes de la sentencia fuera del WHERE, se puede utilizar SQL dinamico con la opción poco recomendada porque el motor no puede optimizar nada, y porque se corre el peligro de sufrir un ataque de SQL Injection de montar la sentencia en un string, y ejecutarlo directamente con EXEC.

Un ejemplo de lo que se puede hacer, pero es mejor evitar:

SET @SQLQuery= 'SELECT ' + cast(@funcion as NVARCHAR(50)) + '('+ cast(@campo as NVARCHAR(50)) +') 
                FROM ' +cast(@bd as NVARCHAR(50))+ '.' + cast(@tabla as nvarchar(50))
EXECUTE(@SQLQuery)

Un ejemplo para obtener el mismo resultado, pero de manera más segura y eficiente. Se define además una variable de salida para recuperar un valor de la query.

Sin duda esta es la opción más recomendada para ejecutar SQL dinámico en procedures de T-SQL:

DECLARE
@Tabla sysname,
@Funcion nvarchar(50),

SET @SQLQuery='SELECT @valor=' + @funcion + '(' + @campo + ') FROM ' + QUOTENAME(@tabla)
EXECUTE sp_executesql @SQLQuery, N'@campo nvarchar(50),@valor bigint OUTPUT', 
                      @campo=@campo,@valor=@valor OUTPUT
SELECT @valor as valor

Destacar que @tabla y @funcion son variables locales que se pueden preparar dinámicamente dentro del procedure, e incluirlas directamente en la sentencia y que @campo sería, por ejemplo, un parámetro del procedure.

@Tabla es de tipo sysname porque se utiliza como nombre de tabla en el FROM, si se definiera como NVARCHAR en algunos casos podrían producirse errores de ejecución.

Referencias de otros sitios con más detalles y ejemplos: MSQLTipsCodeproject

 

Enviar un comentario nuevo

El contenido de este campo se mantiene como privado y no se muestra públicamente.

Si no estás registrado recuerda que tu comentario entrará en una cola de aprobación.

Más información sobre opciones de formato

Enviando este formulario, acepta la política de privacidad de Mollom.

 



 

  BI   |    CRM     |    CMS    |    Tendencias en software empresarial    |    Cloud computing  |    Software libre    |   Internet    |    Movilidad y apps