Changes to a table-valued function called by a stored procedure are not recognized?

Question

Hey all

I have a stored procedure sp that calls a table-valued function tvf. Sometimes I modify the tvf but when subsequently executing sp, the output from sp is the same as before the modification. It seems like it is cached or compiled or something. If I make some dummy change to the sp, then I get the right output of the sp.

Is there some way, I can overcome this problem? In Oracle it is possible to re-compile all stored procedures, but I haven't been able to figure out how to do this in SQL Server?

Any help is highly appreciated.

Answer 1

You can use sp_recompile to recompile a stored procedure but afaik, what you describe shouldn't happen.

Could you post the udf? Only scenario I can think of would be when the udf always returns the same result, regardless of input parameters.