TSQL - Can I 'recompile' all TVFs?

[Pho]

Not sure what the correct wording is, but essentially if I have one TVF which uses another TVF, i.e:

TVF1:

SELECT * FROM Table WHERE ID = @ID

And another TVF which calls TVF1 at some point, i.e.:

SELECT Col1, Col2 FROM TVF1(100)

and then I make some changes to TVF1 (i.e. change the column order) TVF2 returns data from the wrong columns - so if Col2 was originally the second column, but is now the third column in TVF1 I'll see the data from the second column still.

I can fix this by opening up TVF1 and executing the 'ALTER FUNCTION' code, but this is annoying and easy to forget when moving to the live site.

Does anyone have any ideas?

Cheers.

 

[Deanje]

Try using the stored proc sp_recompile.

 

[Pho]

Sorry for the late reply.

Looks like this only works for stored procedures, not TVFs .

 

[SimonCHere]

Don't use select *. Name the columns? then your second one shouldn't have any problems.

 

[Pho]

True, but if we change the 'base' TVF and have a lot of other TVFs using it then it would be a pain to update everything else still - and would still require a manual update of the TVF with the new column names.

The base TVF is essentially pulling out the raw data from the tables and takes a few filter parameters to shape what data we get. The derived TVFs then perform different calculations on this data depending on what you want to do with it - so you'll end up with all the base data + new columns with the calculations results. We don't need to change these too often so it's not a huge deal but would be nice if it could be done automatically so to speak.