Alter Stored Procedure with errors

Question

Alter Stored Procedure with errors

cschlieve

SSC Veteran

Points: 217
More actions
July 8, 2013 at 10:38 am

#279644

So here is my problem. We have a development Database that has tables that are in the middle of some long term changes that won't be done for at least a quarter if not longer. I have a Stored Procedure that needs to be changed "NOW" but this stored procedure uses the altered tables so I cannot alter the change the should go out now that uses the correct production table columns.
Is there anyway I can convince the database to ignore the fact that these columns don't exist/have altered names in the database and Alter the stored procedure anyway?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No.

You can create a procedure that refers to tables that don't exist, but if the tables do exist, the columns referenced must all be there.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323504 More actions · Answer 2

cschlieve (7/8/2013)
So here is my problem. We have a development Database that has tables that are in the middle of some long term changes that won't be done for at least a quarter if not longer. I have a Stored Procedure that needs to be changed "NOW" but this stored procedure uses the altered tables so I cannot alter the change the should go out now that uses the correct production table columns.
Is there anyway I can convince the database to ignore the fact that these columns don't exist/have altered names in the database and Alter the stored procedure anyway?

sounds like you need to modify the procedure carefully, and not blindly take the developer version of this procedure.

What is preventing you from taking the original version of the stored procedure, and modifying THAT to have the newer logic, without takeing the developer version of the procedure, which references other tables?

The details on something like this are crucial, but that's the gist of it...don't blindly accept code that will fail in a specific schema.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ed Wagner SSC Guru Points: 287024 More actions · Answer 3

Not as far as I know. I know exactly what you mean, as I've encountered it several times myself. What I've done in this situation is:

1. Start with the most up-to-date procedure.

2. Comment out the parts that don't apply to the current update and include a keyword in the comment such as FUTURE or something similar.

3. Do the updates the stakeholder wants done now.

4. Release it to production.

5. Copy that and use it as a base for your new development. To get it caught back up to where it was for the new updates, look for the word FUTURE in your code and uncomment those blocks.

I know this is a pain, but you're definitely not alone. The alternative is to have several different versions of the procedure on different machines and I've found synchronizing the different versions to be more painful than commenting things out.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions