Take the 2-minute tour ×
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.

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.

Thanks.

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
add comment

2 Answers

up vote 4 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 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:

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
    
(d) has a very interesting workaround... –  Brian Mains Sep 25 '12 at 1:31
add comment

Stored procedures do not validate the objects that they reference upon creation - they only validate it the first time they are compiled (usually this is when they are first run). This enables you to do things such as this. The only downside is that SQL Server cannot correctly build the dependency tree if any of the objects that it references are not created at the time the stored procedure is run. If you are getting validation errors then it will be for some other reason. The same seems to apply to functions as well, I have just run this script against a database that does not contain a test2 table and they created perfectly fine, they error when I run them though:

create procedure test
as
begin
select * from test2;
end;
go
create function fn_test()
returns int
as
begin
declare @id int;
set @id = (select 1 from test2);
return 1;
end
go

I hope this helps you.

share|improve this answer
    
@MrBrownstone I have a table missing a column, and it says it can't create the function because of that... –  Brian Mains Sep 24 '12 at 16:43
add comment

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.