0

I've got the following (very simplified) stored procedure:

CREATE PROCEDURE [dbo].[sp_Search]
    @MatchGender varchar(6)

AS
BEGIN

SELECT 
    * 
FROM [tblUsers]
WHERE [UserGender] in (@MatchGender)

END

GO

I want to be able to match either Male, Female or both, so I'm using the "IN' clause.

When I test my stored procedure, I run the following:

exec [sp_Search] 'F'

Works fine. Now, how would I run that for both genders? When I try:

exec [sp_Search] ('F', 'M')

It doesn't work. What's the proper syntax? Please note, some of these matches have a couple dozen options, so assume a user can select 6 or 7 out of a possible 20. Not all of my variables are as simple as M/F).

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • The structure of the IN clause is a comma-delimited list; the parameter you've defined is just a simple string. The ugly approach would be to construct a string with your target list and then EXEC it, eg "select * from tblusers where usergender in (" + @matchGender + ")" – David W Jun 10 '15 at 16:54
  • possible duplicate of [SQL : in clause in storedprocedure:how to pass values](http://stackoverflow.com/questions/1525126/sql-in-clause-in-storedprocedurehow-to-pass-values) – Iłya Bursov Jun 10 '15 at 16:56

1 Answers1

0

I won't pretend this is the ideal answer from an overall design perspective, but here's one approach to interpret what you're trying:

CREATE PROCEDURE [dbo].[sp_Search]
    @MatchGender nvarchar(max)

AS
BEGIN
Declare @statement nvarchar(max)

Set @Statement = "select * from [tblUsers] where UserGender in (" + @MatchGender +")"

exec @statement

end

Now, some obvious caveats to this is that you must make sure the parameter string is properly formatted, eg "'M','F'" so it will form a proper list when EXEC'ed.

Again, this might not be the ideal notion from an overall design, (lots of security issues, sql injection risks, etc) but this should illustrate one way to accomplish what you're trying. Untested :)

David W
  • 10,062
  • 34
  • 60
  • I find it totally frustrating that Microsoft creates a multiselect front-end control, and this is their viable solution for doing something with it on the back-end? Nothing against your answer, it's just that all the other answers I've seen were equally "cludge-y". – Johnny Bones Jun 10 '15 at 17:03
  • LOL I surely understand. I just try to remember that MS bought SQL Server from Sybase a Loooooooong time ago, and Sybase didn't have much reason to think about MS multiselects back then, so kludges are often where we're left :) But in this era, you'd think there'd be a more elegant way - a readonly table parameter isn't too bad, either. – David W Jun 10 '15 at 17:06