Select Range Query

[anilcool]

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.

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

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 and pass a range of values ?

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

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

Thanks in advance.

-Anil

[Serge Rielau]

an******@gmail.com wrote:

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.

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

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 and pass a range of values ?

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

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

Thanks in advance.

-Anil

CREATE PROCEDURE DB2. sp_gettotry_logs (
IN myid CHARACTER(36)
)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE txt VARCHAR(1000);
DECLARE stmt STATEMENT;
DECLARE c1 CURSOR WITH RETURN FOR stmt;
SET txt = 'select * from db2.playlogs_ where playerid_ in ('
|| myid || ')';
PREPARE stmt FROM txt;
OPEN c1;
END

CALL DB2.sp_gettotry_logs('19,01');

I assume that playerid_ is a numeric, if it's a string you need to
escape the quotes when passing a constant:
CALL DB2.sp_gettotry_logs('''19'',''01''');

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Tonkuma]

> CREATE PROCEDURE DB2. sp_gettotry_logs (

IN myid CHARACTER(36)
) (SNIP)
CALL DB2.sp_gettotry_logs('19,01');

Because, data type of string constant('19,01') is VARCHAR,
Perhaps, you need to change data type of argument like this:

CALL DB2.sp_gettotry_logs(CHAR('19,01'))

So, it's better to define input parameter as VARCHAR.

CREATE PROCEDURE DB2. sp_gettotry_logs (
IN myid VARCHAR(36)

[anilcool]

Worked.. thanks :)

-Anil