By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,017 Members | 951 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,017 IT Pros & Developers. It's quick & easy.

SQL Procedure Question

P: n/a
I am new to creating DB2 SQL Procedures and I need a little help.

This works perfectly when I am just using it through my SQL Editor:
SELECT * FROM SALESTABLE
WHERE VENDOR LIKE 'ELK' || '%' AND CUSTOMER LIKE 'HOO' || '%'

It will not work for my procedure. Here it is:

CREATE PROCEDURE SALES_HISTORY (
IN I_VENDOR CHAR(6) ,
IN I_CUST CHAR(6))
DYNAMIC RESULT SETS 1
LANGUAGE SQL

BEGIN

DECLARE CURSOR1 CURSOR FOR SELECT * FROM SALESTABLE
WHERE VENDOR LIKE I_VENDOR || '%' AND CUSTOMER LIKE I_CUST || '%' ;

OPEN CURSOR1 ;
END ;

If I call the procedure as CALL SALES_HISTORY('ELK','HOO') it returns
nothing.
Interesting enough, if I pass in the full vendor and customer numbers
it works. CALL SALES_HISTORY('ELK050','HOO050')

Can anybody help me create this procedure so I can pass only part of
the vendor and customer numbers and still retrieve results?

My DB2 database resides on an ISeries server running V5R4.

Nov 16 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
use VARCHAR. Yo are looking for 'ELK %' but you want 'ELK%'

IN I_VENDOR VARCHAR(6) ,
IN I_CUST VARCHAR(6))

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Nov 16 '06 #2

P: n/a
I knew it had to be something simple I was overlooking. Thank you
Serge!!

Serge Rielau wrote:
use VARCHAR. Yo are looking for 'ELK %' but you want 'ELK%'

IN I_VENDOR VARCHAR(6) ,
IN I_CUST VARCHAR(6))

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html
Nov 16 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.