sql - Executing a stored procedure using the 'IN' clause -

i've got following (very simplified) stored procedure:

create procedure [dbo].[sp_search]     @matchgender varchar(6)  begin  select      *  [tblusers] [usergender] in (@matchgender)  end  go 

i want able match either male, female or both, i'm using "in' clause.

when test stored procedure, run following:

exec [sp_search] 'f' 

works fine. now, how run both genders? when try:

exec [sp_search] ('f', 'm') 

it doesn't work. what's proper syntax? please note, of these matches have couple dozen options, assume user can select 6 or 7 out of possible 20. not of variables simple m/f).

i won't pretend ideal answer overall design perspective, here's 1 approach interpret you're trying:

create procedure [dbo].[sp_search]     @matchgender nvarchar(max)  begin declare @statement nvarchar(max)  set @statement = "select * [tblusers] usergender in (" + @matchgender +")"  exec @statement  end 

now, obvious caveats must make sure parameter string formatted, eg "'m','f'" form proper list when exec'ed.

again, might not ideal notion overall design, (lots of security issues, sql injection risks, etc) should illustrate 1 way accomplish you're trying. untested :)