Building Simple SQL Stored Procedure

[rzagars]

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_MYTABLE ( IN BeginDate DATE,
IN EndDate DATE )
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure

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

DECLARE GLOBAL TEMPORARY TABLE myResults AS
(SELECT
MYTABLE.LAST_NAME,
MYTABLE.FIRST_NAME,
MYTABLE.BIRTH_DATE
FROM ADMINISTRATOR.MYTABLE AS MYTABLE
WHERE MYTABLE.BIRTH_DATE >= BeginDate
AND MYTABLE.BIRTH_DATE < 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.myResults;

--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.MYTABLE
ON COMMIT DELETE ROWS
NOT LOGGED
IN USERTEMPSPACE1;

INSERT INTO SESSION.myResults
SELECT
MYTABLE.LAST_NAME,
MYTABLE.FIRST_NAME,
MYTABLE.BIRTH_DATE
FROM ADMINISTRATOR.MYTABLE AS MYTABLE
WHERE MYTABLE.BIRTH_DATE >= BeginDate
AND MYTABLE.BIRTH_DATE < 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.myResults AS MYRESULTS;

-- Cursor left open for client application
OPEN cursor1;

Or just

SELECT * FROM SESSION.myResults;

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

[Serge Rielau]

rz*****@tampabay.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_MYTABLE ( IN BeginDate DATE,
IN EndDate DATE )
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure

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

DECLARE GLOBAL TEMPORARY TABLE myResults AS
(SELECT
MYTABLE.LAST_NAME,
MYTABLE.FIRST_NAME,
MYTABLE.BIRTH_DATE
FROM ADMINISTRATOR.MYTABLE AS MYTABLE
WHERE MYTABLE.BIRTH_DATE >= BeginDate
AND MYTABLE.BIRTH_DATE < 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.myResults;

--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.MYTABLE
ON COMMIT DELETE ROWS
NOT LOGGED
IN USERTEMPSPACE1;

INSERT INTO SESSION.myResults
SELECT
MYTABLE.LAST_NAME,
MYTABLE.FIRST_NAME,
MYTABLE.BIRTH_DATE
FROM ADMINISTRATOR.MYTABLE AS MYTABLE
WHERE MYTABLE.BIRTH_DATE >= BeginDate
AND MYTABLE.BIRTH_DATE < 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.myResults AS MYRESULTS;

-- Cursor left open for client application
OPEN cursor1;

Or just

SELECT * FROM SESSION.myResults;

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

[Bob Zagars]

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_MYTABLE
( IN BeginDate DATE, IN EndDate DATE )
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure

------------------------------------------------------------------------
BEGIN
DECLARE GLOBAL TEMPORARY TABLE myResults AS
(SELECT
MYTABLE.LAST_NAME,
MYTABLE.FIRST_NAME,
MYTABLE.BIRTH_DATE
FROM ADMINISTRATOR.MYTABLE AS MYTABLE)
DEFINITION ONLY NOT LOGGED;

INSERT INTO SESSION.myResults
SELECT
MYTABLE.LAST_NAME,
MYTABLE.FIRST_NAME,
MYTABLE.BIRTH_DATE
FROM ADMINISTRATOR.MYTABLE AS MYTABLE
WHERE MYTABLE.BIRTH_DATE >= BeginDate
AND MYTABLE.BIRTH_DATE < EndDate;

-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT
MYRESULTS.LAST_NAME,
MYRESULTS.FIRST_NAME,
MYRESULTS.BIRTH_DATE
FROM SESSION.myResults AS MYRESULTS;

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

Bob Zagars
Senior Software Engineer

[Serge Rielau]

> CREATE PROCEDURE LDBS_SP_SEL_MYTABLE

( IN BeginDate DATE, IN EndDate DATE )
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE GLOBAL TEMPORARY TABLE myResults AS
(SELECT
MYTABLE.LAST_NAME,
MYTABLE.FIRST_NAME,
MYTABLE.BIRTH_DATE
FROM ADMINISTRATOR.MYTABLE AS MYTABLE)
DEFINITION ONLY NOT LOGGED;

INSERT INTO SESSION.myResults
SELECT
MYTABLE.LAST_NAME,
MYTABLE.FIRST_NAME,
MYTABLE.BIRTH_DATE
FROM ADMINISTRATOR.MYTABLE AS MYTABLE
WHERE MYTABLE.BIRTH_DATE >= BeginDate
AND MYTABLE.BIRTH_DATE < EndDate;
BEGIN -- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT
MYRESULTS.LAST_NAME,
MYRESULTS.FIRST_NAME,
MYRESULTS.BIRTH_DATE
FROM SESSION.myResults 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

[Bob Zagars]

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

[Serge Rielau]

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

[Bob Zagars]

T-SQL examples

DECLARE @MyCursor CURSOR;
EXEC dbo.currency_cursor @currency_cursor = @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

[Serge Rielau]

Bob Zagars wrote:

T-SQL examples

DECLARE @MyCursor CURSOR;
EXEC dbo.currency_cursor @currency_cursor = @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_cursor = FOR SELECT * FROM T;
inside the dbo.currency_cursor 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