SQL Server Table-Valued Functions Funny

Putting it here so that I have it somewhere that I can remember. Stored procedures and table-valued functions in SQL Server 2008 (I don’t know if this applies to earlier versions as I’ve not checked) behave slightly differently when using a view as their data source.

I have a view called cb_ClientView that does some jiggery-pokery to return the results that I need across several tables. It’s used in several places so that I don’t have to repeat the code several times.

Now I have both a stored procedure and a table-valued function that return data from the view. In both cases the SQL to utilise the view is pretty simple:

SELECT *
FROM cb_ClientView
WHERE @ShowEdited=1 OR cb_ClientView.IsEdited=0

Now I know that I should specify the exact columns that I want to return and not use the wildcard for columns but this method allows me to change the view to return other bits of data without having to modify all the stored procedures that use the code.

Which is exactly what I had to do. However this doesn’t work if you have a function!

Running the table-valued function still uses the old view definition. Unless you open the function in SQL Server Management Studio and run the statement, effectively altering the function, to be exactly what it was in the first place.

Seems a little inconsistent to me!