Executing dynamic SQL in a SQLServer 2005 function

Question

I will preface this question by saying, I do not think it is solvable. I also have a workaround, I can create a stored procedure with an OUTPUT to accomplish this, it is just easier to code the sections where I need this checksum using a function.

This code will not work because of the Exec SP_ExecuteSQL @SQL calls. Anyone know how to execute dynamic SQL in a function? (and once again, I do not think it is possible. If it is though, I'd love to know how to get around it!)

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

It "ordinarily" can't be done as SQL Server treats functions as deterministic, which means that for a given set of inputs, it should always return the same outputs. A stored procedure or dynamic sql can be non-deterministic because it can change external state, such as a table, which is relied on.

Given that in SQL server functions are always deterministic, it would be a bad idea from a future maintenance perspective to attempt to circumvent this as it could cause fairly major confusion for anyone who has to support the code in future.