December 6, 2007 at 11:24 am
This is some problem for Inline table functions (the behavior seems to be similar in SQL Server 2000 and 2005):
I have these 2 inline table functions (very simplified, for the sake of the example):
CREATE FUNCTION dbo.Test_InlineTable (@p1 varchar(100))
RETURNS TABLE
AS
RETURN
(SELECT Test1=@p1,Test2='First String')
GO
CREATE FUNCTION dbo.Table_InlineOut ()
RETURNS TABLE
AS
RETURN
(SELECT * from dbo.Test_InlineTable('Some string here!'))
GO
I run:
select * from dbo.Table_InlineOut()
and I get:
Test1 Test2
------------------- --------------
Some string here! First String
Then I change the internal UDF:
ALTER FUNCTION dbo.Test_InlineTable (@p1 varchar(100))
RETURNS TABLE
AS
RETURN
(SELECT Test1=@p1,Test2='First String',Test3='Second String')
GO
and I run again
select * from dbo.Table_InlineOut()
I get the same old result:
Test1 Test2
------------------- --------------
Some string here! First String
It looks like the external UDF is insensitive if I add new field to the internal UDF.
However, if I remove a field from the internal UDF I get this error message:
Msg 4502, Level 16, State 1, Line 1
View or function 'dbo.Table_InlineOut' has more column names specified than columns defined.
This is an extremely simplified case and just reruning ALTER FUNCTION for the external UDF solves the problem. However, in practice there might be quite a lot of external UDFs that use that internal UDF. If I change the internal UDF and the caller doesn't get updated is not good! So, any idea how can I work around this issue?
December 7, 2007 at 1:43 am
Hi,
It's a MS bug, the same problem is with views, but in the views you have just to use WITH SCHEMABINDING.
For functions I think you have to alter the two functions.
check the link http://sqlserver2000.databases.aspfaq.com/why-do-i-have-problems-with-views-after-altering-the-base-table.html
Regards,
Ahmed
December 7, 2007 at 10:22 am
Thanks. I think that is just an exemple of bad practice! I can use SCHEMABINDING here too, once I get rid of that SELECT *! At least with schemabinding I won't be able to change the inside UDF. It's a little more complicated if I want to change it, but at least it keeps things together!
Gabriela
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply