在 SQLServer 2005 函数中执行动态 SQL [英] Executing dynamic SQL in a SQLServer 2005 function
问题描述
我先说这个问题,我不认为它是可以解决的.我还有一个解决方法,我可以创建一个带有 OUTPUT 的存储过程来完成此操作,使用函数对需要此校验和的部分进行编码会更容易.
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.
由于 Exec SP_ExecuteSQL @SQL
调用,此代码将不起作用.任何人都知道如何在函数中执行动态 SQL?(再一次,我认为这是不可能的.如果是,我很想知道如何解决它!)
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
推荐答案
它通常"无法完成,因为 SQL Server 将函数视为确定性的,这意味着对于给定的一组输入,它应该始终返回相同的输出.存储过程或动态 sql 可能是不确定的,因为它可以更改依赖的外部状态,例如表.
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.
鉴于 SQL 服务器中的函数始终是确定性的,从未来的维护角度来看,试图规避这一点是个坏主意,因为这可能会给将来必须支持代码的任何人造成相当大的混乱.
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屋!