Passing multvalue parameters in stored procedure

[vcujackson]

ALTER PROCEDURE [dbo].[usp_px1]
-- Add the parameters for the stored procedure here
@birthmth char(2)
AS
select coreid,birthmth,attrtype
from core inner join attribute
on attrid = coreid
where attrtype = 'px1'
and brthmn = @brthmn

When executed, this passes a single parameter for a birth month. Now I want to pass multiple birth months (ie 08,09,10) while still keeping the inner join with the attribute table. How can I accomplish this?

 

[Crowley16]

you could change the input parameter to a varchar(xxx) and then use the in check, i.e. ... and brthmn in (@birthmth)

or you could use the complicated method of splitting the input string into a normalised temp table and then joining on that table...

--------------------
Procrastinate Now!

 

[bborissov]

Because months are limited number (12) you could change your SP to look like this:

ALTER PROCEDURE [dbo].[usp_px1]
    @birthmth1  char(2) = '',
    @birthmth2  char(2) = '',
    @birthmth3  char(2) = '',
    @birthmth4  char(2) = '',
    @birthmth5  char(2) = '',
    @birthmth6  char(2) = '',
    @birthmth7  char(2) = '',
    @birthmth8  char(2) = '',
    @birthmth9  char(2) = '',
    @birthmth10 char(2) = '',
    @birthmth11 char(2) = '',
    @birthmth12 char(2) = ''
AS
   select coreid,
          birthmth,
          attrtype
     from core
    inner join attribute on attrid = coreid
    where attrtype = 'px1' and
          brthmn IN (@birthmth1,
                     @birthmth2,
                     @birthmth3,
                     @birthmth4,
                     @birthmth5,
                     @birthmth6,
                     @birthmth7,
                     @birthmth8,
                     @birthmth9,
                     @birthmth10,
                     @birthmth11,
                     @birthmth12)

NOT TESTED!


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

Crowley16,
You can't pass a comma delimited string and use it directly in IN ().
You must split it first (as you suggested) and then join returned table.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[RyanEK]

Here are several ways to pass multiple variables into a stored procedure Passing list/array to SP

Ryan