You can create stored procedures that reference objects that don't exist yet (e.g. tables and functions). You cannot create stored procedures that reference columns in objects that already exist. This is the double-edged sword of deferred name resolution - SQL Server gives you the benefit of the doubt in some cases, but not all. See Erland's ides for
SET STRICT_CHECKS ON; to get some ideas of the places this works and the places it breaks:
(And how he'd like the polar opposite of what you're after - you want to allow anything to compile regardless of existence, and he wants every single column or table to be checked.)
There is no setting like
SET DEFERRED_NAME_RESOLUTION OFF; though it has been asked for:
And there is no setting like
You could get around this in a few ways, including:
(a) use dynamic SQL in the affected stored procedure(s).
(b) build a stub for
CREATE PROCEDURE with nothing in it, then run the rest of your script, then run an
ALTER PROCEDURE which has the real body (in essence, deploy the procedure in two phases).
(c) make your deployment tool smarter about the order of operations. If table changes require the presence of a function, script those changes last. Schema comparison tools like RedGate's SQL Compare are pretty good about generating scripts for you in the proper dependency order. You don't mention what tool you're using, but if it's not doing this...
(d) Martin Smith has an interesting workaround here, but I haven't played with it.