Checking to see if a Stored Procedure exist in DB2 SQL

maikeru-sama · #1 (**permalink**) 01-02-09, 18:26

Hi, I am new to DB2 and I come from an SQL Server background.

In Transact-SQL, when making a Stored Procedure, I always had an "IF EXISTS" clause at the top to check to see if the Stored Procdure already existed and if so Drop it.

How can I do the same thing with a Stored Procedure in DB2?

I have a database project in Visual Studio.Net and it is connected to the AS400. When I make a Stored Procedure, everything is fine. However, whenever I modify the Stored Procedure and Re-apply it to the AS400, it correctly errors and states that the Stored Procedure exists already.

Thanks in advance

Marcus_A · #2 (**permalink**) 01-02-09, 19:01

You can't with regular SQL. You could query the syscat.procuedures veiw or do a conditional create if you use a scripting language or a SP, but you cannot do it with simple SQL.

Just do the drop and ignor the error if it doesn't exist.

maikeru-sama · #3 (**permalink**) 01-02-09, 20:35

Quote:

Originally Posted by Marcus_A

You can't with regular SQL. You could query the syscat.procuedures veiw or do a conditional create if you use a scripting language or a SP, but you cannot do it with simple SQL.

Just do the drop and ignor the error if it doesn't exist.

Marcus, thanks for answering my question.

So would I do something like this:

Code:

DROP PROCEDURE TEST

CREATE PROCEDURE TEST()

I don't have access to the simple stored procedure I wrote today at work, so I apologize for the simplicity.

Marcus_A · #4 (**permalink**) 01-03-09, 01:55

Yes, but don't forget your statement terminator. For a script that contains SP's you must use something other than the default ";" since many statements within the SP have a ";".

nidm · #5 (**permalink**) 01-07-09, 13:58

maybe you also need a 'commit' after the 'drop proc'

maikeru-sama · #6 (**permalink**) 01-07-09, 19:20

I will look into that when I get to work.

When I tried to do:

Code:

DROP PROCEDURE sp_Test();

CREATE PROCEDURE sp_Test()

I got an error. I believe it said the Create Statement wasn't expected.

Regardless, if the Stored Procedure doesn't exist the first time, it will error out anyway.

The Procedure is not going to change much, so it won't be a big deal.

Marcus_A · #7 (**permalink**) 01-08-09, 00:47

You cannot use ";" as a terminator in a script that creates SP's or triggers. That is becasue the semi-colon is used as a terminator of lines within the SP. Use something else like "@" to terminate the create SP statement (and all other statemnets in the script) so that ";" can be used a lines within the SP.

When you run the script, you will have to change the default terminator:

db2 -td@ -vf <file-name>

stolze · #8 (**permalink**) 01-12-09, 03:26

Or use:

Code:

--#SET TERMINATOR @

in the script itself

Internet Services

Web Development

Digital Marketing

Consumer Tech