473,578 Members | 2,818 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Building Simple SQL Stored Procedure

I have just started working on DB2 which the syntax is a lot different.
I am trying to create a SQL stored procedure for generating reports.
I want to use temporary database which in this example it is trult not
needed but due to past experience it is key to developing good reports
for cliebt/server application. Here is my example which of course
dopes not work and documentation on the Internet is limited. Please if
anyone can clarify this procedure please let me know.

CREATE PROCEDURE LDBS_SP_SEL_MYT ABLE ( IN BeginDate DATE,
IN EndDate DATE )
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure

------------------------------------------------------------------------
BEGIN

DECLARE GLOBAL TEMPORARY TABLE myResults AS
(SELECT
MYTABLE.LAST_NA ME,
MYTABLE.FIRST_N AME,
MYTABLE.BIRTH_D ATE
FROM ADMINISTRATOR.M YTABLE AS MYTABLE
WHERE MYTABLE.BIRTH_D ATE >= BeginDate
AND MYTABLE.BIRTH_D ATE < EndDate)
DEFINITION ONLY NOT LOGGED;

BEGIN
--Declare client cursor
DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
SELECT
LAST_NAME,
FIRST_NAME,
BIRTH_DATE
FROM SESSION.myResul ts;

--Cursor left open for client application
OPEN cursor1;
END;
END

Also I tried to use the INSERT INTO

BEGIN
DECLARE GLOBAL TEMPORARY TABLE myResults
LIKE ADMINISTRATOR.M YTABLE
ON COMMIT DELETE ROWS
NOT LOGGED
IN USERTEMPSPACE1;

INSERT INTO SESSION.myResul ts
SELECT
MYTABLE.LAST_NA ME,
MYTABLE.FIRST_N AME,
MYTABLE.BIRTH_D ATE
FROM ADMINISTRATOR.M YTABLE AS MYTABLE
WHERE MYTABLE.BIRTH_D ATE >= BeginDate
AND MYTABLE.BIRTH_D ATE < EndDate;

END

Also on the second code example I tried to show the result and am not
sure what is right.

-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT
MYRESULTS.LAST_ NAME,
MYRESULTS.FIRST _NAME,
MYRESULTS.BIRTH _DATE
FROM SESSION.myResul ts AS MYRESULTS;

-- Cursor left open for client application
OPEN cursor1;

Or just

SELECT * FROM SESSION.myResul ts;

Both of these code block come up with errors. I have to develop a
standard for returning data which is readable in SQL format. I could
write one for other SQL engines which is a lot simpler but DB2 has
other standards.

Best Regards,

Bob Zagars

Mar 20 '06 #1
7 3710
rz*****@tampaba y.rr.com wrote:
I have just started working on DB2 which the syntax is a lot different.
I am trying to create a SQL stored procedure for generating reports.
I want to use temporary database which in this example it is trult not
needed but due to past experience it is key to developing good reports
for cliebt/server application. Here is my example which of course
dopes not work and documentation on the Internet is limited. Please if
anyone can clarify this procedure please let me know.

CREATE PROCEDURE LDBS_SP_SEL_MYT ABLE ( IN BeginDate DATE,
IN EndDate DATE )
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure

------------------------------------------------------------------------
BEGIN

DECLARE GLOBAL TEMPORARY TABLE myResults AS
(SELECT
MYTABLE.LAST_NA ME,
MYTABLE.FIRST_N AME,
MYTABLE.BIRTH_D ATE
FROM ADMINISTRATOR.M YTABLE AS MYTABLE
WHERE MYTABLE.BIRTH_D ATE >= BeginDate
AND MYTABLE.BIRTH_D ATE < EndDate)
DEFINITION ONLY NOT LOGGED;

BEGIN
--Declare client cursor
DECLARE cursor1 CURSOR WITH RETURN TO CLIENT FOR
SELECT
LAST_NAME,
FIRST_NAME,
BIRTH_DATE
FROM SESSION.myResul ts;

--Cursor left open for client application
OPEN cursor1;
END;
END

Also I tried to use the INSERT INTO

BEGIN
DECLARE GLOBAL TEMPORARY TABLE myResults
LIKE ADMINISTRATOR.M YTABLE
ON COMMIT DELETE ROWS
NOT LOGGED
IN USERTEMPSPACE1;

INSERT INTO SESSION.myResul ts
SELECT
MYTABLE.LAST_NA ME,
MYTABLE.FIRST_N AME,
MYTABLE.BIRTH_D ATE
FROM ADMINISTRATOR.M YTABLE AS MYTABLE
WHERE MYTABLE.BIRTH_D ATE >= BeginDate
AND MYTABLE.BIRTH_D ATE < EndDate;

END

Also on the second code example I tried to show the result and am not
sure what is right.

-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT
MYRESULTS.LAST_ NAME,
MYRESULTS.FIRST _NAME,
MYRESULTS.BIRTH _DATE
FROM SESSION.myResul ts AS MYRESULTS;

-- Cursor left open for client application
OPEN cursor1;

Or just

SELECT * FROM SESSION.myResul ts;

Both of these code block come up with errors. I have to develop a
standard for returning data which is readable in SQL format. I could
write one for other SQL engines which is a lot simpler but DB2 has
other standards.

You have to combine the two attempts. The secret is in here:
DEFINITION ONLY
So DB2 will declare the temp for you but it will NOT do the inserts.
So you have to do them.
Assuming you want the result as a resultset you then open the cursor.

So:
DECLARE GLOBAL TEMPORARY .... AS SELECT ...
INSERT INTO SESSION.... AS SELECT...
BEGIN
DECLARE cur1 CURSOR WITH RETURN...
OPEN cur1;
END;

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 20 '06 #2
If DEFINITION ONLY Just declares the structure and then you can use
INSERT INTO to populate the temp table. This script should work but
does not and returns error. My table and overall structure is simple
but the syntax is evading me to start development.
CREATE PROCEDURE LDBS_SP_SEL_MYT ABLE
( IN BeginDate DATE, IN EndDate DATE )
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure

------------------------------------------------------------------------
BEGIN
DECLARE GLOBAL TEMPORARY TABLE myResults AS
(SELECT
MYTABLE.LAST_NA ME,
MYTABLE.FIRST_N AME,
MYTABLE.BIRTH_D ATE
FROM ADMINISTRATOR.M YTABLE AS MYTABLE)
DEFINITION ONLY NOT LOGGED;

INSERT INTO SESSION.myResul ts
SELECT
MYTABLE.LAST_NA ME,
MYTABLE.FIRST_N AME,
MYTABLE.BIRTH_D ATE
FROM ADMINISTRATOR.M YTABLE AS MYTABLE
WHERE MYTABLE.BIRTH_D ATE >= BeginDate
AND MYTABLE.BIRTH_D ATE < EndDate;

-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT
MYRESULTS.LAST_ NAME,
MYRESULTS.FIRST _NAME,
MYRESULTS.BIRTH _DATE
FROM SESSION.myResul ts AS MYRESULTS;

-- Cursor left open for client application
OPEN cursor1;
END

Bob Zagars
Senior Software Engineer

Mar 20 '06 #3
> CREATE PROCEDURE LDBS_SP_SEL_MYT ABLE
( IN BeginDate DATE, IN EndDate DATE )
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE GLOBAL TEMPORARY TABLE myResults AS
(SELECT
MYTABLE.LAST_NA ME,
MYTABLE.FIRST_N AME,
MYTABLE.BIRTH_D ATE
FROM ADMINISTRATOR.M YTABLE AS MYTABLE)
DEFINITION ONLY NOT LOGGED;

INSERT INTO SESSION.myResul ts
SELECT
MYTABLE.LAST_NA ME,
MYTABLE.FIRST_N AME,
MYTABLE.BIRTH_D ATE
FROM ADMINISTRATOR.M YTABLE AS MYTABLE
WHERE MYTABLE.BIRTH_D ATE >= BeginDate
AND MYTABLE.BIRTH_D ATE < EndDate;
BEGIN -- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT
MYRESULTS.LAST_ NAME,
MYRESULTS.FIRST _NAME,
MYRESULTS.BIRTH _DATE
FROM SESSION.myResul ts AS MYRESULTS;

-- Cursor left open for client application
OPEN cursor1; END; END


DECLARE cursor is part of the header of a compound statement. It can't
be in the middle of the block.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 20 '06 #4
That is different then SQL Server and Sybase T-SQL which a cursor can
be implied anywhere it just has to be opened and close plus DEALLOCATE
the cursor. So technically I could insert the DECLARE cursor at the
beginning before the INSERT INTO. Actually this works and I can
develop reports from this structure, I am just interested in how your
database store procedure work. On T-SQL a BEGIN and END is a set of
statements processed together which I can declare anywhere within.
Example

IF ...
BEGIN

...multiple statements including cursors...

END
ELSE...
BEGIN

...multiple statements

END

Anyway thanks for all your help. I can now develop some report using
your engine.

Bob Zagars
Senior Software Engineer

Mar 20 '06 #5
Bob Zagars wrote:
That is different then SQL Server and Sybase T-SQL which a cursor can
be implied anywhere it just has to be opened and close plus DEALLOCATE
the cursor. So technically I could insert the DECLARE cursor at the
beginning before the INSERT INTO. Actually this works and I can
develop reports from this structure, I am just interested in how your
database store procedure work. On T-SQL a BEGIN and END is a set of
statements processed together which I can declare anywhere within.

Same in DB2.
See my solution.
All you need to do is "push" a block. BEGIN..END can be added anywhere
within the SQL Procedure and it can be nested. To add local exception
handlers, variables, conditions and cursors.

BTW, there is an "open question" of mine on how this works in SQL
Server. Maybe you can clarify. :-)
As you have noted in DB2 you can specify WITH RETURN TO CALLER and WITH
RETURN TO CLIENT. The first returns the cursor to the invoker of the
stored procedure, which may be the client or a stored procedure. The
resultset can then be consumed by the invoking procedure.
The later returns the result set straight to the client.
It appears that "implicit" result set creation in T-SQL is always RETURN
TO CLIENT. So how does one pass a results set to a calling procedure in
T-SQL?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 20 '06 #6
T-SQL examples

DECLARE @MyCursor CURSOR;
EXEC dbo.currency_cu rsor @currency_curso r = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO

There is a CALLER statement when creating a procedure

CREATE PROCEDURE ssp_vendor_info _all
WITH EXECUTE AS CALLER

The first one is a way of walking through the result set from within a
stored procedure. The second I am not sure but does specify the CALLER
argument and might fit your needs. Thank you again for all the
information.

Best Regard,

Bob Zagars
Senior Software Engineer

Mar 20 '06 #7
Bob Zagars wrote:
T-SQL examples

DECLARE @MyCursor CURSOR;
EXEC dbo.currency_cu rsor @currency_curso r = @MyCursor OUTPUT;
WHILE (@@FETCH_STATUS = 0)
BEGIN;
FETCH NEXT FROM @MyCursor;
END;
CLOSE @MyCursor;
DEALLOCATE @MyCursor;
GO OK, but that does not work with implicit cursors, right?
You need to explicitly type:
SET @currency_curso r = FOR SELECT * FROM T;
inside the dbo.currency_cu rsor stored procedure.
There is a CALLER statement when creating a procedure
CREATE PROCEDURE ssp_vendor_info _all
WITH EXECUTE AS CALLER

Different thing, we call that "invoker's rights" in DB2, nothing to do
with result sets.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 20 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
6044
by: Lili | last post by:
I'm having problems creating a simple stored procedure in DB2. Can someone help? Here is the screen dump when I tried to load the stored procedure. Thanks for any help. Create procedure update_salary (in emp_number char(6), in rate integer) language sql begin update employee
2
5446
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
3
13964
by: Nils Magnus Englund | last post by:
Hi, I've made a HttpModule which deals with user authentication. On the first request in a users session, it fetches data from a SQL Server using the following code: using (SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings)) {
1
1629
by: E.T. Grey | last post by:
I have been busting my nut over this for pretty much most of the day and it is driving me nuts. I posted this to an mySQL ng yesterday and I have not had any response (I'm pulling my hair out here). Its really a very simple stored procedure but I simply can't seem to get it to work. I have a simple table misc_data described as ff: ...
4
1464
by: James | last post by:
How can I see/set the timeout for a postback? I have a button click event which loops through a DataSet and calls a stored procedure for every record in the dataset. This can be a very large number, and locally it's working fine. However, when I move to our development server, the exact same code pointing to the same database redirects to a...
1
7498
by: peaceburn | last post by:
Hi, I'm gonna pull my hair in the coming days with these DB2 stored procedures. So the issue, let's assume a simple stored procedure like this : CREATE PROCEDURE MYSCHEMA.PROCEDURE1 ( ) DYNAMIC RESULT SETS 1 ------------------------------------------------------------------------
1
4297
by: gregoryday | last post by:
I am battling to get a stored procedure to build: I have a stored procedure working in SQL Server 2000, and now need to port it to DB2. I will be accessing the stored procedure via VB.NET. The stored procedure reads a value from a table, increments the value by 1 and writes it back into the table. The table is called...
1
1813
by: Ted | last post by:
In MS SQL I used the following to create a stored procedure. USE AdventureWorks; GO IF OBJECT_ID ( 'HumanResources.usp_My_Search', 'P' ) IS NOT NULL DROP PROCEDURE HumanResources.usp_My_Search; GO CREATE PROCEDURE HumanResources.usp_My_Search @searchstring varchar(40) AS
7
1803
by: CSharper | last post by:
Yesterday I had a heated discussion with my colleagues on what is a data centric application and having business logic in sql. I have group of people who wants to include all the business logic in the sql stored proc and I for one consider to have most of the business logic in the C# code (specifically in a controller class of MVC). These are...
0
7850
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
6524
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5664
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5343
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3786
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3801
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2293
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1391
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1114
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.