SQL Server – Disabling Schema Checking on Function/Stored Procedure Creation

ddlfunctionssql serversql-server-2008-r2stored-procedures

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.

Best Answer

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:

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.

Related Question