在SQLServer 2005函数中执行动态SQL [英] Executing dynamic SQL in a SQLServer 2005 function

问题描述

我会先说这个问题，说我不认为这是可以解决的。我也有一个解决方法，我可以用OUTPUT创建一个存储过程来完成这个任务，只需使用一个函数对需要校验和的部分进行编码就更容易。

由于 Exec SP_ExecuteSQL @SQL 调用，此代码不起作用。任何人都知道如何在函数中执行动态SQL？ （再一次，我不认为这是可能的，但如果是这样，我很想知道如何绕过它！）

 创建函数Get_Checksum 
（
 @DatabaseName varchar（100），
 @TableName varchar（100）
）
 RETURNS FLOAT 
 AS 
 BEGIN 
 
声明@SQL nvarchar（4000）
声明@ColumnName varchar（100）
声明@i int 
声明@Checksum float 
声明@intColumns表（idRecord int identity（1,1），ColumnName varchar（255））
声明@CS表（MyCheckSum bigint）
 
 Set @SQL = 
'插入@IntColumns（ColumnName）'+ Char（13）+ 
'选择Column_Name'+ Char（13）+ 
'从'+ @DatabaseName +'.Information_Schema.Columns（NOLOCK）'+ Char （13）+ 
'其中Table_Name ='''+ @TableName +''''+ Char（13）+ 
'和Data_Type =''int'''
 
  - 打印@SQL 
 
 exec sp_executeSql @SQL 
 
 Set @SQL = 
'Insert Into @CS（MyChecksum）'+ Char（13）+ 
'选择' 
 
设置@i = 1 
 
虽然存在（
选择1 
从@IntColumns 
其中IdRecord = @i）
开始
选择@ColumnName = ColumnName 
从@IntColumns 
其中IdRecord = @i 
 
设置@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 
 
设置@Checksum =（选择Top 1 MyChecksum Fr （@ Checksum，0）
 
 END 
 GO 
   
 
 
鉴于SQL服务器功能是总是确定性的，从未来的维护角度来看，这将是一个坏主意，试图绕过这个问题，因为它可能会给将来需要支持代码的人造成相当大的困惑。
 
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

解决方案
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.

                        
这篇关于在SQLServer 2005函数中执行动态SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！