Inline table functions and recompilation

Last Post 07 Dec 2007 12:53 AM by SQLUSA. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
gnanau
New Member
New Member

--
06 Dec 2007 09:36 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?
SQLUSA
New Member
New Member

--
07 Dec 2007 12:53 AM
You have to recompile the outer UDF when you alter the inner UDF.

Kalman Toth
www.sqlusa.com
You are not authorized to post a reply.

Acceptable Use Policy