All Forums Database
Matt W. 1 post Joined 12/05
21 Dec 2005
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)BEGINselect count(branch_id) into :valoutfrom branchwhere 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);>>36call dss_tables.mw_test_proc( 'AL', valout);>>6But 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,

Nytewynd 13 posts Joined 12/05
03 Jan 2006

Very helpful. Tagging for later.

You must sign in to leave a comment.