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.

When creating a Stored Procedure in SQL Server you are allowed to refer to tables which do not exist. But, if the table does exist then any column you refer to in the procedure must exist in that table (Deferred Name Resolution).

Is it possble to instruct SQL Server to defer the name resolution of all tables referenced in a procedure irrespective of whether they exist or not? I do want to keep the general syntax checking, so even if it were possible, hacking the sproc definition into a system table isn't an option.

I expect my asking to do this might seem a little bit weird, so here's some background: I auto generate table definitions and sprocs from an application written in C# and it's very difficult for me to change the code to order the changes as SQL needs them. My code "guarantees" that the schema is consistent within a transaction, but currently I can't gurantee that the table columns are defined before I define the sproc which references them.

Below is a canonical example of the SQL created by the C# which "illustrates" the problem I'm trying to solve.

--Say this table already exists.
CREATE TABLE myTable
(
    a NVARCHAR(MAX)
)
GO

--My C# code creates something like this
BEGIN TRAN 
GO

--the sproc gets generated first.
CREATE PROCEDURE mySproc
AS
BEGIN
    SELECT a,b FROM myTable
END

--then the table update
ALTER TABLE myTable
    ADD b nvarchar(MAX)

COMMIT TRAN 

It is possible for me to fix this in the C# code, but I'm hoping for a simple "magic" tweek I can pull in the SQL. This will save a lot of time for me.

share|improve this question
1  
Can't you just process all schema changes before creating/altering any procedures? Why must the procedure exist before the table is correct? –  Aaron Bertrand Feb 21 '12 at 0:47
    
I'm pursing that option in the code now. The way the SQL is generated is fairly complicated (that was a simple example) but it's looking like it's not going to be as much as a PITA as I thought. –  Daniel James Bryars Feb 21 '12 at 1:29
1  
You can get around it of course by stuffing your stored procedures full of dynamic SQL - but I can't imagine generating your script to handle schema changes then stored procedures would be all that difficult. There aren't too many any options exposed to dictate how deferred name resolution works. The only proposal on the books that I know of, or at least that I can infer they're interested in entertaining, is actually the other way - making it MORE strict - see sommarskog.se/strict_checks.html). –  Aaron Bertrand Feb 21 '12 at 1:37
    
Good idea about the dynamic SQL. I've got the same problem for Triggers, Indexes, Views, Sprocs, and Functions. But I've changed the code so that it just makes changes to the Tables, then Indexes, then Triggers, then functions, then sprocs. –  Daniel James Bryars Feb 23 '12 at 13:09
    
I like sommarskog's suggestions, definitely will help avoid bugs. If they did implement a Strict option, then they could also reevaluate all the "Strict ON" sprocs when there is a table change to see if it breaks existing sprocs - obviously you would need then to have a "logical transaction on DDL" so you can then change the Table and Sprocs as one unit. –  Daniel James Bryars Feb 23 '12 at 13:09
show 1 more comment

1 Answer

up vote 4 down vote accepted

No.

I feel really guilty just typing that, but no, sadly. That's the first time I've heard of this use case, and it makes perfect sense. Best to submit a request for it on http://connect.microsoft.com and your grandchildren will be able to do it. ;-)

share|improve this answer
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.