MS-SQL-08: Is it possible to create a view that depends on a function before the function has been created ?

Question

Hey folks,

Using MS-SQL-08:

Is it possible to somehow temporarily disable dependency checking, so that I can create a view that uses a function that does not exist (has not yet been created) at the time of creating the view. [then create the required function, and finally re-enable dependency checking]

So, lets say I have view V, which uses function F, the order I would like to be able to achieve is

turn off dependency checks 1. create V 2. create F turn dependency checks back on

Thanks.

Answer 1

This is called deferred name resolution and while you can create a proc that select from a table if the table does not exists you cannot do that with a view that uses a function

example of the proc

create procedure prTest
as select * from MyTestTable
go

create table MyTestTable(id int)
go

exec prTest

Answer 2

A possible work-around would be to create a dummy (or placeholder) function, create the view using it without schema-binding, then later replace the function with the desired one.

This might not work--I vaguely recall a situation where we altered a table within a view, and then the view would not work until it was "reset" (which could be as simple as issuing an ALTER VIEW statement with the exact same definition). Do a lot of testing, and be prepared to write awkward code.

Answer 3

I don't think this is possible as SQL Server needs to evaluate the view on creation to derive the types for the columns.

Just create a dummy function, or if you know there's going to be function which returns a specific type replace it with something like SELECT CAST(NULL AS tinyiny) as MyFuncPlaceholder