I'm trying to automate the process that executes changes to the SQL Server 2008 R2 database. The process I put in place drops and recreates my stored procedures and functions, as well as run scripts to change the tables/columns/data. Unfortunately, one of the scripts requires one of the functions to be put in place first. But I can't run all stored proc/function changes first because it relies on columns being added from the tables/columns/data change scripts first.

I was wondering if it was possible to run stored procedures and functions without SQL Server validating the columns used in the definition of the function/SP? I tried looking but couldn't find a condition or command to enable this.

  • It sounds like you may just need to rearrange the object creation in your scripts. Sep 24, 2012 at 13:35
  • @shark It's that a change script requires a dependency on a function being there, which it wasn't at the time... to do that would require manual intervention; I wanted something more automatic. Sep 24, 2012 at 13:37

2 Answers 2


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 that don't exist yet in objects that do 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 ideas 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 IGNORE ALL_RESOLUTION;.

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.

  • 1
    Wow, that Martin Smith hack is brilliantly clever. I would feel dirty using it now, but in my early 20s would have. Apr 18, 2019 at 19:41

You can create a stored procedure that deletes or renames the object in question first and then runs your original stored procedure as dynamic SQL. This way you don't have to rewrite the actual stored procedure to use dynamic SQL.

The code below runs a stored procedure that references columns that don't yet exist (Expense_Super_Compare)

IF OBJECT_ID('Expense_Super_Compare_Results', 'U') IS NOT NULL
     EXEC('DROP TABLE Expense_Super_Compare_Results');

exec('exec dbo.Expense_Super_Compare');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.