How to avoid the rework in SPs

Question

How to avoid the rework in SPs

jones bastin

Old Hand

Points: 348
More actions
January 19, 2005 at 5:33 am

#183941

Hi Friends
I created a table with a column as Integer. I used this table (field)in 25 stored procedures.Now the requirement is changed (ie) column is converted to varchar(150) field.
I m asked to change l the work done in 25 SPs with related to the that particular column .
Is there any this way to avoid this re work in Sql Server2k if i follow any stratagies ?
Thanks in Advance
SQL Bud (Jones)

Viewing 6 posts - 1 through 5 (of 5 total)

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

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 1

Hi,

I am afraid there is not a shortcut for this. Next time use user defined data types, so you will have to DROP and CREATE SPs again after the table is restructured.

Regards,

Goce.

jones bastin Old Hand Points: 348 More actions · Answer 2

Hi Goce

Thanks for the reply , but im still not clear. I want to avoid the recreation or altering of sps again.

pls answer this query if any one faces this situation

Thanks

Jones

philcart SSC-Forever Points: 47794 More actions · Answer 3

Jones

The best strategy involves not coding stored procedures until the database schema is finalised. Realistically this will probably never happen

To make the changes a bit easier for you. Script all the stored procedures that need changing to a single file. Open this file in Query Analyzer, do a find and replace, run the script to re-create the procedures.

--------------------
Colt 45 - the original point and click interface

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 4

Hi all,

I still think that you cannot (or a least should not) avoid altering those SPs, because a data type change is not an issue that can pass without major code modifications. Imagine this situation: you client application allow the user to enter characters in fields where a stored procedure still expects integers. So, some "invalid data type conversion" error will occur (the message text depends on the programming platform).

During the development, I think that the pro-active approach (having scripts to create all database objects) is much much better then the retro-active approach (design objects graphically and generate scripts later). My experience tells me that Enterprise Managere doesn't generate usefull scripts, since it does not consider inter-object dependencies. So, at the end you usually get script that, when executed, causes many dependency errors. The other way (using scripts do define everything in the database) ensures you that, once they execute without errors, your database is created correctly.

Now, about your problem: if it is a development database, it is not late to generate scripts in EM and manually re-arange CREATE PROCEDURE statements in the correct order. If it is a production database, you should generate ALTER PROCEDURE statements for affected SPs, and I think that the order of statements is not so important here. Either way, you should test all of them.

I saw a different approach on InterBase platform. IB script generator tool first generates empty SPs (something like header files in C, declaration only), so they still don't reference each other in code. Later, after CREATE statements for all objects are generated, ALTER PROCEDURE statements are added in the script. But now, all procedures exists and the order of statements does not cause dependency problems. This is still not the ideal way, but at least is easier for maintenance and the script execution is clean.

Regards,

Goce.

jones bastin Old Hand Points: 348 More actions · Answer 5

Hi Goce and Phil

Thank you , Now i ve understood the Procedure to handle this situation .

I really happy to get cleared these doubts

Regards

Jones