3

Is it possible to create or alter a stored procedure in SQL Server 2005* that doesn't compile the procedure?

(* - yes, unfortunately, we still need to support 2005 as our minimum standard)

I have a situation where it would be exceedingly useful to be able to create/alter a stored procedure that is syntactically valid but not semantically valid (due to incorrect table/column names)... which I can then alter accordingly within SSMS.

I've just had a thought, and it's a pretty simple solution... comment out the entire contents of the stored procedure when it's being altered/created.

Using Ctrl+K Ctrl+C will comment out each line individually - which would be better than using /* */ around the entire thing, as that will fail if there are any instances of /* */ in the stored procedure already.

I would still be very interested to hear if there is an actual way to tell SSMS to ignore invalid table/view/column names.

Thanks to @marcelo for his answer, and it does appear that using the name of a non-existent table will allow you to create the stored procedure.

However, it does not allow you to create a stored procedure if you use a non-existent column in an existing table

Improve this question

2 Answers 2

Reset to default
6

What you are referring to is deferred name resolution and is further explained here Deferred Name Resolution and Compilation.

Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.

Basically, no you are not able to alter the parsers rules for what objects must exist at parse and compile time. I've previously had this issue when deploying SPs which reference linked servers and those linked servers did not have a valid connection. To work around it I updated the linked sever logic to be dynamic SQL.

Improve this answer
1
  • Thanks @Pixelated, not the answer I wanted, but at least it's definitive! The important part of the page (which you might want to quote it your answer is this: Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.
    – freefaller
    Commented Oct 31, 2014 at 10:38
1

I believe that in sql server 2005 you can create a stored procedure that references a table that does not exist. I have just done it, see my example below.

--set parseonly on

select @@version
--Microsoft SQL Server 2005 - 9.00.5000.00 (X64) 
--Dec 10 2010 10:38:40 
--Copyright (c) 1988-2005 Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)



use dba
go

create procedure mysp
as 
begin

select getdate()
select * from mytablethatdoesnot exist

end

enter image description here

enter image description here

Improve this answer
10
  • Thanks for your response @marcelo. Hmmm, yes, it does appear to be possible for tables, but not for non-existant columns in existing tables. Any ideas?
    – freefaller
    Commented Oct 31, 2014 at 9:56
  • @freefaller No, the only way to bypass compile-time checking for columns is to put the code referencing those columns inside dynamic SQL. Not sure I understand the benefit of bypassing these checks when you can't call that stored procedure until the column exists anyway...
    – Aaron Bertrand
    Commented Oct 31, 2014 at 13:18
  • Thanks @Aaron for your input. It's complex to explain and I'm sure I would be ripped apart if I did explain, but it's for development purposes - where I have multiple databases that share 95% of tables/columns, but I need to transfer sprocs over including the 5%. Would be a lot easier if I could transfer and then alter, rather than alter then transfer
    – freefaller
    Commented Oct 31, 2014 at 14:32
  • @AaronBertrand I run into the problem frequently because of one method we use to "backup" a SP before making code changes. We add a date stamp to the end of the SP name and then create the old version under a new name. If the table structure underneath has changed we can't create the "backup" version.
    – Kenneth Fisher
    Commented Oct 31, 2014 at 19:57
  • @Kenneth See, I would rather keep old versions of the stored procedure offline (source control) than cluttering my database. YMMV, obviously. :-)
    – Aaron Bertrand
    Commented Oct 31, 2014 at 20:02

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.