Range Select query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

anilcool@gmail.com - 10 Feb 2006 00:48 GMT

Hi all,

This is probably a simple problem for most of you.. Let me know if you
have any pointers for me. I am new to DB2.

In my stored procedure I want to select records that match a range of
values.
I need some help figuring with writing the stored procedure definition.

=========================================================

create PROCEDURE DB2. sp_gettotry_logs (
IN myid
CHARACTER(36)
)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR
select * from db2.playlogs_ where playerid_ in
( myid ) ;

OPEN c1;
END

===========================================================

How do I call this procedure with a range of values ?

I've tried:
call db2.sp_gettotry_logs( '19','01')

This doesn't work because the function it is expecting only one
parameter ... but I want to get record results where playerid is either
'19' or '01'.

Thanks in advance.

-Anil

Reply to this Message

Knut Stolze - 10 Feb 2006 07:20 GMT

> Hi all,
>
[quoted text clipped - 31 lines]
> parameter ... but I want to get record results where playerid is either
> '19' or '01'.

Any particular need to put this into a stored procedure as this always comes
with a well-defined set of parameters.

A simple SELECT would do the job for you:

SELECT *
FROM db2.playlogs_
WHERE playerid_ IN ( '19', '01' )

Or you stored the values in a table and then use a sub-select in the IN
predicate:

SELECT *
FROM db2.playlogs_
WHERE playerid_ IN ( SELECT ...
FROM ... )

Or you build a dynamic SQL statement inside the procedure, prepare that, and
open a cursor over it.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message