update stored procedures in SQL scripts

[Bruudje]

Hello,

Is it possible to update a stored procedure with sql scripts?

I know it's possible to update it with the alter command. But with that command you must rewrite the whole procedure. I just need to add a statement, because the content differce and must stay in the procedure.

I hope someone can help me.
thnx in advance

 

[essa2000]

Dear ;

Why are you updating a Stored procedure instead of droping it first and then re-create. I use this approach to update Stored Procedures. If there is any reason then explain it to me.

Regards,
essa2000

 

[tlbroadbent]

The entire procedure must be compiled when altered or dropped and created. You cannot simply insert a statement. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

essa2000:
One advantage of using ALTER PROC instead of DROP/CREATE (well, at least some folks consider it an advantage), is that ALTER PROC retains any permissions that have previously been established for the SP. In other words, any permissions in place will remain intact, whereas with DROP/CREATE the permissions have to be granted all over again.

At a minimum, that's a minor inconvenience if you have a record of the SP and accompanying permissions stored in a script outside the database, but it's a pain if your DBA staff assigns permissions through the EM graphical interface.

 

[Bruudje]

Hmmm...

So I have to alter the procedures. I know I can view the text (sql statement) of the stored procedure with sp_helptext [stored procedure name]. But how can I get the text (sql statement) from the procedure in the altered procedure? Or is this not possible?


I hope someone knows, because otherwise i must write a lot of sql scripts and if I have to change a statement i must rewrite all the scripts :'( That's a lot of work.

thnx in advance
R.Breukers

 

[bperry]

I think this is what you want.....

One way to do it is from Enterprise Manager.
Go to Stored Procedures, and in the right-hand pane highlight all the ones you want to script.

Then RightClick/All Tasks/Generate Script.

This build CREATE PROC syntax, with all the SP code, for each SP you selected. There's an option to create the scripts all in one big test file, or as separate files for each SP.

Then you can change CREATE PROC to ALTER PROC in each one.


(Sometimes if it's just one SP that I want the code for, I won't bother creating script like this. I'll just go into the SP (i.e. 'the Properties'), and then copy&paste.)

 

[Bruudje]

Thnx bperry, but that isn't it

It's hard for me to explain this in English. Euhm... I will try again.

I'm writing scripts for easy implementation in a database. We work with "portals" (Is this good English?). These portals are optional in the stored procedure dependent of which portals are chosen. Each portal has it's own scripts. If a portal script is run, then a the stored procedures are created for that portal. But if there is another portal needed. Then the existing stored procedures must be updated with the sql code for the new portal.

for example:

CREATE PROCEDURE sp_example
@portal varchar(10)
AS

if @portal = 'CUP'
BEGIN
SELECT name
FROM example
WHERE portal = 'CUP'
END


With this script i need to ad the code:

if @portal = 'DOP'
BEGIN
SELECT name
FROM example
WHERE portal = 'DOP'
END


Can this be done?

Maybe if I alter the stored procedure sp_helptext to insert the extra lines of code into the created (temporary) table 'commentText'. But is it possible to execute the text if it's in a table per line, so it will create the new stored procedure? And if so, how can I do this?

If someone can help me with this, I'm thankfull for the rest of my life

thnx in advance
R.Breukers