1

I have this SQL:

Select * from Table1
Where instructor_name in (@Instructors)

The problem is that the instructor name in the table is in the format LastName, FirstName. I'm not sure how to handle the comma so that I can find "Schmoe, Joe" in the database. Does anyone have an idea how I can do so?

Thanks!

Michael Robinson
  • 1,106
  • 3
  • 13
  • 40
  • Can you provide a little more detail about what your parameter values are going in to this? Are you trying to use a multi-value parameter? – Jamie F Mar 18 '14 at 03:58
  • I want to be able to use a multi-values parameter as I see the clients wanting to report on more than one instructor at a time. Both the parameter and the column values will have the "firstname, lastname" format. – Michael Robinson Mar 18 '14 at 12:45
  • Is the query embedded in the report? If so, can you use a stored procedure? You can make this work with a stored procedure - see http://stackoverflow.com/questions/1256925/ssrs-multi-value-parameter-using-a-stored-procedure – Ian Preston Mar 18 '14 at 12:51

1 Answers1

0

You can send in a list of params and the create a function to break the values into a table in which case the following commands will work:

WHERE (@CommaLists IS NULL OR SomeTable.SomeValue IN (SELECT ID FROM dbo.IR_CreateIntIDTable(@CommaList)))

Ross Bush
  • 14,648
  • 2
  • 32
  • 55
  • It doesn't sound like the OP has a column (or parameter) with multiple values. If so, they should use the Multi-value parameter option available in SSRS. – Jamie F Mar 18 '14 at 03:58
  • I do want to be able to have the client search on more than one instructor at a time, if that is what they want, if that helps any. – Michael Robinson Mar 18 '14 at 12:46