michael.kaufmann (1/18/2011)
I don't understand why the following code is not working (using SQL Server 2005; pasted just my amended query--you'll need the original one, too):
--Select 3
SET @inputValue = '''Hardy''' + ', ' + '''Rocky'''
select @inputValue -- added to view the output of the variable
SELECT * FROM @student WHERE StudentName IN (@inputValue) -- not working
It is working, just not as you expect it. If you look at the code above, you'll see that the IN list consists of exactly one member - the variable @inputValue. SQL Server will not care or interpret the content of that variable, but simply search the @student table for rows with a StudentName that matches that value - so you get all rows returned for students whose name is equal to [font="Courier New"]'Hardy', 'Rocky'[/font] (and I hope for their sake that no student has been given that name!)
To get SQL Server to find both Hardy and Rocky, you need to supply two arguments, seperated by a comma. The comma has to be in the IN list, not in the contents of the variables.
--Select 3
SET @inputValue1 = 'Hardy';
SET @inputValue2 = 'Rocky';
--select @inputValue -- added to view the output of the variable
SELECT * FROM @student WHERE StudentName IN (@inputValue1, @inputValue2);