sql-server-2005 - sentencias - sql dinamico sql server

Ejecución de SQL dinámico en una función SQLServer 2005

Question

Prefacio a esta pregunta diciendo: No creo que pueda resolverse. También tengo una solución, puedo crear un procedimiento almacenado con una SALIDA para lograr esto, es más fácil codificar las secciones donde necesito esta suma de comprobación usando una función.

Este código no funcionará debido a las llamadas Exec SP_ExecuteSQL @SQL . ¿Alguien sabe cómo ejecutar SQL dinámico en una función? (y una vez más, no creo que sea posible. Si es así, ¡me encantaría saber cómo evitarlo!)

Create Function Get_Checksum
(
    @DatabaseName      varchar(100),
    @TableName         varchar(100)
)
RETURNS FLOAT
AS
BEGIN

 Declare @SQL        nvarchar(4000)
 Declare @ColumnName varchar(100)
 Declare @i          int
 Declare @Checksum   float
 Declare @intColumns table (idRecord int identity(1,1), ColumnName varchar(255))
 Declare @CS         table (MyCheckSum bigint)

 Set @SQL = 
        ''Insert Into @IntColumns(ColumnName)'' + Char(13) + 
        ''Select Column_Name'' + Char(13) +
        ''From   '' + @DatabaseName + ''.Information_Schema.Columns (NOLOCK)'' + Char(13) +
        ''Where  Table_Name = '''''' + @TableName + '''''''' + Char(13) +
        ''       and Data_Type = ''''int'''''' 

 -- print @SQL

 exec sp_executeSql @SQL

 Set @SQL = 
        ''Insert Into @CS(MyChecksum)'' + Char(13) + 
        ''Select ''

 Set @i = 1

 While Exists(
       Select 1
       From   @IntColumns
       Where  IdRecord = @i)
 begin
       Select @ColumnName = ColumnName
       From   @IntColumns
       Where  IdRecord = @i

       Set @SQL = @SQL + Char(13) + 
            CASE WHEN @i = 1 THEN 
                 ''    Sum(Cast(IsNull('' + @ColumnName + '',0) as bigint))''
                 ELSE
                 ''    + Sum(Cast(IsNull('' + @ColumnName + '',0) as bigint))''
            END

       Set @i = @i + 1
 end

 Set @SQL = @SQL + Char(13) + 
      ''From '' + @DatabaseName + ''..'' + @TableName + '' (NOLOCK)''

 -- print @SQL

 exec sp_executeSql @SQL

 Set @Checksum = (Select Top 1 MyChecksum From @CS)

 Return isnull(@Checksum,0)

END
GO

Answer 1

"Normalmente" no se puede hacer ya que SQL Server trata las funciones como deterministas, lo que significa que para un conjunto dado de entradas, siempre debe devolver las mismas salidas. Un procedimiento almacenado o sql dinámico puede ser no determinista porque puede cambiar el estado externo, como una tabla, en la que se confía.

Dado que en SQL las funciones del servidor siempre son deterministas, sería una mala idea desde el punto de vista del mantenimiento futuro intentar eludir esto, ya que podría causar una gran confusión para cualquiera que tenga que admitir el código en el futuro.

Answer 2

Aquí está la solución

Solución 1: Devuelve la cadena dinámica de Función y luego

Declare @SQLStr varchar(max) 
DECLARE @tmptable table (<columns>)
set @SQLStr=dbo.function(<parameters>)
insert into @tmptable
Exec (@SQLStr)

select * from @tmptable

Solución 2: llamar a funciones anidadas pasando parámetros.

Answer 3

Debido a que las funciones tienen que jugar muy bien con el optimizador de consultas, existen bastantes restricciones sobre ellas. Este enlace se refiere a un artículo que analiza las limitaciones de las UDF en profundidad.

Answer 4

Gracias a todos por las respuestas.

Ron: para tu información, usar eso arrojará un error.

Estoy de acuerdo en que no hacer lo que originalmente pretendía es la mejor solución, decidí tomar una ruta diferente. Mis dos opciones fueron usar la sum(cast(BINARY_CHECKSUM(*) as float)) o un parámetro de salida en un procedimiento almacenado. Después de la velocidad de prueba unitaria de cada uno, decidí ir con sum(cast(BINARY_CHECKSUM(*) as float)) para obtener un valor de suma de comprobación comparable para los datos de cada tabla.

Answer 5

Puede evitar esto llamando a un procedimiento almacenado extendido, con todas las molestias y problemas de seguridad concomitantes.

http://decipherinfosys.wordpress.com/2008/07/16/udf-limitations-in-sql-server/

http://decipherinfosys.wordpress.com/2007/02/27/using-getdate-in-a-udf/