|
|
|
Forum Newbie
Group: Forum Members
Last Login: 1/3/2006 10:07:00 AM
Posts: 1,
Visits: 1
|
|
I'd like to create a stored procedure that can accept a string with multiple values as a parameter, and then put that into an IN clause in the query. Here's an example of what I'm trying to do:
CREATE PROCEDURE dss_tables.mw_test_proc(varlist varchar(200), OUT valout smallint)
BEGIN
select count(branch_id)
into :valout
from branch
where mail_state in ( :varlist );
END;
The idea would be to pass in a list of states ('PA','OH','AL') and get back a total count of branches that are in those states.
The stored proc will build OK, and if you pass in one branch it works fine:
call dss_tables.mw_test_proc( 'PA', valout);
>>36
call dss_tables.mw_test_proc( 'AL', valout);
>>6
But if I try to pass in two or more branches, I always get 0 back. I've tried doubling-up the single quotes several different ways, but with no luck:
call dss_tables.mw_test_proc( 'AL'',''PA', valout);
call dss_tables.mw_test_proc( '''AL'',''PA''', valout);
Any suggestions would be much appreciated.
Thanks,
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 8/3/2006 2:56:00 PM
Posts: 13,
Visits: 1
|
|
|
Very helpful. Tagging for later.
|
|
|
|