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);