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

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: MSQLTips, Codeproject