Passing the value for an IN clause to a stored procedure.

Database

Passing the value for an IN clause to a stored procedure.

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,
1 REPLY
Enthusiast

Re: Passing the value for an IN clause to a stored procedure.

Very helpful. Tagging for later.