If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Checking to see if a Stored Procedure exist in DB2 SQL

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2009
Posts: 35
Checking to see if a Stored Procedure exist in DB2 SQL

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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,713
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.
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2009
Posts: 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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,713
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 ";".
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: May 2003
Posts: 113
maybe you also need a 'commit' after the 'drop proc'
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2009
Posts: 35
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.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: May 2003
Location: USA
Posts: 5,713
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>
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jan 2007
Location: Jena, Germany
Posts: 2,721
Or use:
Code:
--#SET TERMINATOR @
in the script itself
__________________
Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On