似乎当您使用 ALTER 函数修改函数的列顺序时,SQL 不会更新依赖函数,并且可能导致返回完全不正确的结果。
有没有办法强制依赖函数自动更新,而无需手动完成每个函数?
举个例子:
create function dbo.Test1 ()
returns table
AS
return
(
select 1 as [One], 2 as [Two]
)
GO
create function dbo.Test2 ()
returns table
AS
return
(
select * from Test1()
)
GO
这些工作按预期工作:
select * from Test1()
select * from Test2()
两者都返回:
One Two
----------- -----------
1 2
现在,如果我们修改函数:
alter function dbo.Test1 ()
returns table
AS
return
(
select 2 as [Two], 1 as [One]
)
GO
并查询结果:
select * from Test1()
返回:
Two One
----------- -----------
2 1
正如预期的那样,但是:
select * from Test2()
返回:
One Two
----------- -----------
2 1
因此,我们现在有旧的列排序,但新位置中的值 - 这意味着此示例中的值被转置。
在处理 UDF 时需要使用sp_refreshsqlmodule
。这将刷新元数据并返回正确的列。
EXEC sp_refreshsqlmodule 'dbo.Test2'
sp_refreshsqlmodule将更新 UDF 的元数据。UDF 的元数据(例如参数或数据类型)可能会因为基础对象的更改而过时 - 这就是示例中发生的情况。我已经使用您的示例对其进行了测试,并且它按预期工作。
有关sp_refershsqlmodule
的更多信息,请参阅此处。
正如Matthew Brophy指出的那样,正确的解决方案是不在UDF中使用SELECT *
- 而是明确地列出SELECT
中的列名。