Resaving / Rebuilding User Functions

I have about 10 User Defined Functions that all use another Function that we
can call funct1. These Functions in turn are used by around 50 Stored
Procedures. I recently made a change to funct1 to return an extra field and
it broke many of the stored procs and functions that called it. If I resaved
the functions then they started working again, even though no change was
made. I suppose they had cached the fields they were expecting from funct1
and needed to "rebuild" the list.
Is there some type of dbcc command that would automatically force everything
to be "rebuilt" or "resaved"? I tried DBCC FREEPROCCACHE but that evidently
only empties the execution plans.You can use sp_recompile to force a recompilation of the stored procedure on
next execution.
-oj
"Greg Steele" <Greg Steele@.discussions.microsoft.com> wrote in message
news:DEDFA89D-5592-42F3-A8F8-F40CFEE20919@.microsoft.com...
>I have about 10 User Defined Functions that all use another Function that
>we
> can call funct1. These Functions in turn are used by around 50 Stored
> Procedures. I recently made a change to funct1 to return an extra field
> and
> it broke many of the stored procs and functions that called it. If I
> resaved
> the functions then they started working again, even though no change was
> made. I suppose they had cached the fields they were expecting from funct1
> and needed to "rebuild" the list.
> Is there some type of dbcc command that would automatically force
> everything
> to be "rebuilt" or "resaved"? I tried DBCC FREEPROCCACHE but that
> evidently
> only empties the execution plans.|||Also, make sure that you are not using "SELECT *" in your functions,
because bad things may happen, for example:
USE tempdb
GO
CREATE FUNCTION dbo.FirstFunction()
RETURNS TABLE AS RETURN
SELECT 1 A, 2 B
GO
CREATE FUNCTION dbo.SecondFunction()
RETURNS TABLE AS RETURN
SELECT * FROM dbo.FirstFunction()
GO
CREATE PROCEDURE Procedure1
AS
SELECT A, B FROM dbo.SecondFunction()
GO
EXEC Procedure1
GO
ALTER FUNCTION dbo.FirstFunction()
RETURNS TABLE AS RETURN
SELECT 1 A, 3 C, 2 B
GO
EXEC Procedure1
EXEC sp_recompile 'Procedure1'
EXEC Procedure1
EXEC sp_recompile 'SecondFunction'
EXEC Procedure1
GO
ALTER FUNCTION dbo.SecondFunction()
RETURNS TABLE AS RETURN
SELECT * FROM dbo.FirstFunction()
GO
EXEC Procedure1
GO
DROP FUNCTION FirstFunction, SecondFunction
DROP PROCEDURE Procedure1
As you can see from the above example, if you are using "SELECT *" (in
a view or in-line function) and you add a new column before another
column (or change the order of the columns), this messes-up things
pretty badly: it returns data from the wrong columns; this cannot be
fixed by using sp_recompile, only by altering the UDF that contains
"SELECT *".
Razvan