Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
    
It sounds like you may just need to rearrange the object creation in your scripts. – Thomas Stringer Sep 24 '12 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. – Brian Mains Sep 24 '12 at 13:37
up vote 8 down vote accepted

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 ides for SET STRICT_CHECKS ON; to get some ideas of the places this works and the places it breaks:

http://www.sommarskog.se/strict_checks.html

(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:

http://connect.microsoft.com/sql/127152

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.

share|improve this answer
    
"You cannot create stored procedures that reference columns in objects that already exist." - This seems wrong. Isn't that the point of creating a stored procedure? So you can retrieve data from columns in objects that exist? – Zack Jan 28 at 21:18
1  
@Zack /sigh, updated my wording, thanks and sorry. – Aaron Bertrand Jan 28 at 21:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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