No Record Found When Passing Comma Separated List to IN Operator?

Question

No Record Found When Passing Comma Separated List to IN Operator?

heyzain

Old Hand

Points: 351
More actions
April 21, 2010 at 12:56 am

#146593

Hi Folks,
I am using SQL 2005 and my query goes like this:
DECLARE @StartDate Varchar(50)
DECLARE @EndDate Varchar(50)
DECLARE @BehaviorAverage Float
DECLARE @Unit Varchar(50)
SET @StartDate = '03/21/2010'
SET @EndDate = '04/21/2010'
SET @Unit = '''Alpha'',''Bravo'''
-- This does not work
SELECT @BehaviorAverage = AVG(BehaviorAverage) FROM V_Dashboard WHERE Unit IN(@Unit) AND DateOfEvaluation >= Convert(DateTime, @StartDate) AND DateOfEvaluation <= Convert(DateTime, @EndDate)
-- But this works
SELECT @BehaviorAverage = AVG(BehaviorAverage) FROM V_Dashboard WHERE Unit IN('Alpha','Bravo') AND DateOfEvaluation >= Convert(DateTime, @StartDate) AND DateOfEvaluation <= Convert(DateTime, @EndDate)
Please tell what i am doing wrong?. I need to pass the parameter @Unit to IN operator and the first query to work...
Any help would be highly appreciated.
Thanks!
Zain.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

IN doesn't work that way. Every single value must be a variable on its own, so that you can use IN(@value1, @value2, ..., @valueN).

You could use a table variable to store your values and the use it inside the IN expression:

DECLARE @filters TABLE (

value varchar(500)

)

INSERT INTO @filters VALUES('Tom')

INSERT INTO @filters VALUES('Paul')

SELECT someField

FROM someTable

WHERE someField IN (

SELECT value FROM @filters

)

Hope this helps

Gianluca

-- Gianluca Sartori

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Hi buddy.. you need to use Dynamic SQL for this requirement, IF YOU DONT WANT TO USE GIANLUCA'S IDEA..

I have coded a piece here that might help you with this (using dynamic SQL though ;-)). Follow the comments closely in the code below and use it

DECLARE @StartDate Varchar(50)

DECLARE @EndDate Varchar(50)

--DECLARE @BehaviorAverage Float -- THIS WONT BE REQUIRED HERE

DECLARE @Unit Varchar(50)

SET @StartDate = '03/21/2010'

SET @EndDate = '04/21/2010'

--=== LOOK HOW I USED APOSTROPHES IN @UNIT HERE

SET @Unit = '''Alpha'',''Bravo'''

--===== CREATE A DYNAMIC SQL QUERY TO PASS VALUES FOR IN OPERATOR

--==== LIKE THIS!

DECLARE @SQL_QUERY VARCHAR(4000)

SET @SQL_QUERY = ''

SET @SQL_QUERY = @SQL_QUERY +

--== YOU MUST DECLARE THE @BehaviorAverage INSIDE THE DYNAMIC SQL

'DECLARE @BehaviorAverage Float '+ CHAR(10)+ --==== CHAR(10) IS TO PRODUCE A LINE BREAK

--=== LOOK HOW I HAVE PLACED THE @UNIT HERE

'SELECT @BehaviorAverage = AVG(BehaviorAverage) FROM #tABLE WHERE Unit IN('+@Unit+')'+ CHAR(10)+

'AND DateOfEvaluation >= Convert(DateTime, '''+CAST(@StartDate AS VARCHAR)+''')'+ CHAR(10)+

'AND DateOfEvaluation <= Convert(DateTime, '''+CAST(@EndDate AS VARCHAR)+''')'

--=== PRINT AND CHECK YOUR QUERY STRUCTURE BEFORE EXECUTING IT

PRINT @SQL_QUERY

--=== REMOVE THE COMMENTED CODE BELOW AND EXECUTE IT;

--=== ANY CODE THAT INVOLVE @BehaviorAverage MUST BE PUT IN THE @SQL_QUERY ITSELF

--EXEC (@SQL_QUERY)

Tell us if that worked for u!!

Cheers!

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 3

you need function to split the comma separated values.

check for fnSplit.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

heyzain Old Hand Points: 351 More actions · Answer 4

Gianluca Sartori, Thanks a lot for the help. Your solution worked perfectly.

Regards,

Zain

heyzain Old Hand Points: 351 More actions · Answer 5

Thanks for the help COldCoffee 🙂

Regards,

Zain.