Solved

Creating a stored proc in DB2

Posted on 2011-03-15
3
598 Views
Last Modified: 2012-05-11

How can I write a DB2 stored procedure with 1 input parameter? If so, how can I call it. I am new to DB2 and need your help.

The input parameter is dt and datatype is date
I have a bunch of SQL queries like this --

DECLARE GLOBAL TEMPORARY TABLE Session.DATE
   (EFFECTIVE_DTE DATE NOT NULL)
     ON COMMIT PRESERVE ROWS WITH REPLACE;
   
      Insert  into Session.DATE
    VALUES (dt);
select * from Session.DATE;
select * from session.DATE d1, id t1
where t1.joining_dt = d1.DATE;

I want to put all these statements into a stored procedure. How can I do it?
0
Comment
Question by:sunny82
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 35141437
Hi Sunny,

Is this classwork?  I'll be glad to help, but the kinds of help that we can give is different for classwork than other kinds of assistance.

The general rule is that you can create a stored procedure to do just about anything.  For the most part, put the declaration before and around your code.


CREATE PROCEDURE myprocedure
AS
BEGIN
  -- your code here
END;

One item that will cause you stress, until you realize why, is the SELECT statement.  They won't work in a stored procedure as you have them written.  The SELECT must do something with the results and your SQL doesn't direct the results.  You must SELECT INTO a variable or use the results of the SELECT to do something like INSERT or UPDATE another table.



Good Luck,
Kent
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 35141444
you can find examples here
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0008329.html

calling a stored procedure is done with the call statement
0
 

Author Comment

by:sunny82
ID: 35141660
cool, thanks
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question