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.

Force Modification of a User Defined Function

Is there a way to force the modification of a User Defined Function upon which depend other objects.

This is the message:

Msg 3729, Level 16, State 3, Procedure FunctionName, Line 22

Cannot ALTER 'FunctionName' because it is being referenced by object 'OtherObject'.

I have 48 objects related to the function and each time I need modify the function is a big pain.

Any help will be much appreciated.

Best Regards

ggpnetwork

Sorry, but AFAIK there is no switch to do so. If you have a computede column are something else bound to that function you have to drop and recreate the referencing object / Column or something else.

HTH; Jens Suessmeyer.|||We have currently ran into this issue but found the following solution helpful.

Originally, we were adding a scalar function to a computed column in a table. We received the same error message whenever we attempted to modify the scalar function.

However, if we created a view of the same table and included the scalar function as a column, we were able to modify the scalar without problems. That is to say, we were able to modify the scalar function definition (not the view result) without problems.

Hope this helps,
Daniel

Force Modification of a User Defined Function

Is there a way to force the modification of a User Defined Function upon which depend other objects.

This is the message:

Msg 3729, Level 16, State 3, Procedure FunctionName, Line 22

Cannot ALTER 'FunctionName' because it is being referenced by object 'OtherObject'.

I have 48 objects related to the function and each time I need modify the function is a big pain.

Any help will be much appreciated.

Best Regards

ggpnetwork

Sorry, but AFAIK there is no switch to do so. If you have a computede column are something else bound to that function you have to drop and recreate the referencing object / Column or something else.

HTH; Jens Suessmeyer.|||We have currently ran into this issue but found the following solution helpful.

Originally, we were adding a scalar function to a computed column in a table. We received the same error message whenever we attempted to modify the scalar function.

However, if we created a view of the same table and included the scalar function as a column, we were able to modify the scalar without problems. That is to say, we were able to modify the scalar function definition (not the view result) without problems.

Hope this helps,
Daniel

for passing text type argument to procedure

For parsing data > 8000 char I have defined a procedure that looks something like
create proc myproc @.doc text
as
blah blah blah
go

but now i need to pass text argument to it.
For that I need ::: declare @.newdoc text
and then call ::: exec myproc @.newdoc
but I cannot declare text type variable in the transact sql queries.

Is there a way out
thanksyou normally don't work directly with text, ntext and image data. When working with these types of data you normally pass pointers to the data and use functions/stored procedures to manipulate the data, TEXTPTR, WRITETEXT and UPDATETEXT. Check out "text and image data types" in Books On Line.