Forcing Function recompilation in SQL 2000

A stored procedure in the cache is automatically recompiled when a table it refers to has a table structure change. User defined functions are not. Here's a simplified code sample:

set nocount on
go

create table tmpTest (a int, b int, c int)

insert into tmpTest (a, b, c) values (1, 2, 3)
insert into tmpTest (a, b, c) values (2, 3, 4)
go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fTest]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fTest]
GO

CREATE FUNCTION dbo.fTest (@.a int)
RETURNS TABLE
AS
RETURN (SELECT * from tmpTest where a = @.a)
GO

select * from fTest(1)

CREATE TABLE dbo.Tmp_tmpTest
(
a int NULL,
b int NULL,
d int NULL,
c int NULL
) ON [PRIMARY]
IF EXISTS(SELECT * FROM dbo.tmpTest)
EXEC('INSERT INTO dbo.Tmp_tmpTest (a, b, c)
SELECT a, b, c FROM dbo.tmpTest TABLOCKX')
DROP TABLE dbo.tmpTest
EXECUTE sp_rename N'dbo.Tmp_tmpTest', N'tmpTest', 'OBJECT'

select * from fTest(1)

drop table tmpTest

Running it, the output is:

a b c
-- -- --
1 2 3

Caution: Changing any part of an object name could break scripts and stored procedures.
The OBJECT was renamed to 'tmpTest'.
a b c
-- -- --
1 2 NULL

(I know that "select *" is bad, but it's a lot of legacy code that I'm working with here, and that's how it's written.)

The function doesn't detect that the table has changed in structure, or even that there is no longer a dependency on tmpTest. (Appending a column rather than inserting has the same effect, in that only the first 3 columns are returned.)

DBCC FREEPROCCACHE has no effect, not that I really expected it to, but you never know...

Is there any way, other than dropping and recreating, to force a recompilation of a particular function in memory, or perhaps all functions?

Thanks in anticipation.

Tom

try this example from the Books Online...

USE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'titles_by_author' AND type = 'P') DROP PROCEDURE titles_by_author GO CREATE PROCEDURE titles_by_author @.@.LNAME_PATTERN varchar(30) = '%' WITH RECOMPILE AS SELECT RTRIM(au_fname) + ' ' + RTRIM(au_lname) AS 'Authors full name', title AS Title FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON ta.title_id = t.title_id WHERE au_lname LIKE @.@.LNAME_PATTERN GO |||

Thanks for the suggestion. Just one small point:

It's a function, not a procedure. And WITH RECOMPILE isn't a valid option for a function.

Tom

|||It is because of the SELECT *. Inline table-valued function is similar to view in that the metadata for the columns are persisted at the time of creation of the function. So in your example, the * in the select list will get resolved to table/columns at creation time. You will have to run ALTER FUNCTION or drop/recreate the function to recreate the correct metadata in current versions of SQL Server. SQL Server 2005 SP2 will have a new system stored procedure that can be used to refresh such metadata for SPs, UDFs. This will be similar to sp_refreshview SP. Of course, you wouldn't get into these problems if you specified column names explicitly.

For Xml Path, functions and UDF's

I have created a select statement to return using for xml path which works
without problem.
I now need to add into it the results of a UDF which relates to the data I
already have (parent - child stuff) but I'm running into problems doing this
.
If i include the complete sub query directly in the sql it works without any
issues, but if I take the sub select and put into into a table returning UDF
I get an error "Incorrect syntax near '.'." and the '.' is the parameter
passed to the UDF. The reason for looking at placing the sub-select in a UD
F
is that the same query needs to be included in 20+ sp's all returning Xml
using For Xml Path
I have included pseudo-SQL below showing examples
Anybody have any idea why the UDF won't work'
original sql that works
select a, b,c
from table1
for xml path ('item'), root('root')
modified that will work
select
t1.a,
t1.b,
t1.c,
(select t3.a, t2.e, t2.f
from table2 as t2
join table3 as t3 on t2.id = t3.id
where t3.a = t1.a
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')
modified that won't work
select
t1.a,
t1.b,
t1.c,
(select u1.a, u1.e, u1.f
from dbo.UDF (t1.a) as u1
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')Ok, just found the issue with the UDF in BOL so I understand why I can't pas
s
the parameter in, which is a shame.
Currently investigating creating a scalar UDF that will return xml to see if
thay works.
"Nathan" wrote:

> I have created a select statement to return using for xml path which work
s
> without problem.
> I now need to add into it the results of a UDF which relates to the data I
> already have (parent - child stuff) but I'm running into problems doing th
is.
> If i include the complete sub query directly in the sql it works without a
ny
> issues, but if I take the sub select and put into into a table returning U
DF
> I get an error "Incorrect syntax near '.'." and the '.' is the parameter
> passed to the UDF. The reason for looking at placing the sub-select in a
UDF
> is that the same query needs to be included in 20+ sp's all returning Xml
> using For Xml Path
> I have included pseudo-SQL below showing examples
> Anybody have any idea why the UDF won't work'
> original sql that works
> select a, b,c
> from table1
> for xml path ('item'), root('root')
> modified that will work
> select
> t1.a,
> t1.b,
> t1.c,
> (select t3.a, t2.e, t2.f
> from table2 as t2
> join table3 as t3 on t2.id = t3.id
> where t3.a = t1.a
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')
>
> modified that won't work
> select
> t1.a,
> t1.b,
> t1.c,
> (select u1.a, u1.e, u1.f
> from dbo.UDF (t1.a) as u1
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')|||Well after dispairing that I could resolve the issue and having to post on
the forum I've sorted it.
Final solution was to use a scalar UDF that took a parameter from the select
and returned a xml data type with the data in it which was then amalgamated
by For Xml Path correctly.
"Nathan" wrote:
> Ok, just found the issue with the UDF in BOL so I understand why I can't p
ass
> the parameter in, which is a shame.
> Currently investigating creating a scalar UDF that will return xml to see
if
> thay works.
> "Nathan" wrote:
>

For Xml Path, functions and UDF's

I have created a select statement to return using for xml path which works
without problem.
I now need to add into it the results of a UDF which relates to the data I
already have (parent - child stuff) but I'm running into problems doing this.
If i include the complete sub query directly in the sql it works without any
issues, but if I take the sub select and put into into a table returning UDF
I get an error "Incorrect syntax near '.'." and the '.' is the parameter
passed to the UDF. The reason for looking at placing the sub-select in a UDF
is that the same query needs to be included in 20+ sp's all returning Xml
using For Xml Path
I have included pseudo-SQL below showing examples
Anybody have any idea why the UDF won't work?
original sql that works
select a, b,c
from table1
for xml path ('item'), root('root')
modified that will work
select
t1.a,
t1.b,
t1.c,
(select t3.a, t2.e, t2.f
from table2 as t2
join table3 as t3 on t2.id = t3.id
where t3.a = t1.a
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')
modified that won't work
select
t1.a,
t1.b,
t1.c,
(select u1.a, u1.e, u1.f
from dbo.UDF (t1.a) as u1
for xml path ('internal'), type
)
from table as t1
for xml path ('item'), root('root')
Ok, just found the issue with the UDF in BOL so I understand why I can't pass
the parameter in, which is a shame.
Currently investigating creating a scalar UDF that will return xml to see if
thay works.
"Nathan" wrote:

> I have created a select statement to return using for xml path which works
> without problem.
> I now need to add into it the results of a UDF which relates to the data I
> already have (parent - child stuff) but I'm running into problems doing this.
> If i include the complete sub query directly in the sql it works without any
> issues, but if I take the sub select and put into into a table returning UDF
> I get an error "Incorrect syntax near '.'." and the '.' is the parameter
> passed to the UDF. The reason for looking at placing the sub-select in a UDF
> is that the same query needs to be included in 20+ sp's all returning Xml
> using For Xml Path
> I have included pseudo-SQL below showing examples
> Anybody have any idea why the UDF won't work?
> original sql that works
> select a, b,c
> from table1
> for xml path ('item'), root('root')
> modified that will work
> select
> t1.a,
> t1.b,
> t1.c,
> (select t3.a, t2.e, t2.f
> from table2 as t2
> join table3 as t3 on t2.id = t3.id
> where t3.a = t1.a
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')
>
> modified that won't work
> select
> t1.a,
> t1.b,
> t1.c,
> (select u1.a, u1.e, u1.f
> from dbo.UDF (t1.a) as u1
> for xml path ('internal'), type
> )
> from table as t1
> for xml path ('item'), root('root')
|||Well after dispairing that I could resolve the issue and having to post on
the forum I've sorted it.
Final solution was to use a scalar UDF that took a parameter from the select
and returned a xml data type with the data in it which was then amalgamated
by For Xml Path correctly.
"Nathan" wrote:
[vbcol=seagreen]
> Ok, just found the issue with the UDF in BOL so I understand why I can't pass
> the parameter in, which is a shame.
> Currently investigating creating a scalar UDF that will return xml to see if
> thay works.
> "Nathan" wrote: