IN Operator

Question

IN Operator

per143

Newbie

Points: 2
More actions
September 23, 2003 at 12:43 pm

#83209

hi all, i am using this query now ..
select accountid from ca where csid IN (' + @GMCsids + ')
but this fails when @GMcsids is NULL .. I want to get all the records if GMCsids is NULL, is there any special operator for or do i need to go by adding a IF ELSE statement before this statement??
thnx in advance ..

Viewing 5 posts - 1 through 4 (of 4 total)

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

tkc SSCommitted Points: 1535 More actions · Answer 1

Someone just helped me answer a question similar to this (I presume). If I'm correctly translating the technique someone showed me, then you can do this...



SELECT accountid 

FROM ca 

WHERE csid  = 

  CASE 

  WHEN (@GMCsids IS NULL) THEN csid  

  ELSE @GMCsids

  END

per143 Newbie Points: 2 More actions · Answer 2

Thanks tkc, but this returns only records with Not NULL csid records and moreover

if GMCSids = '5,6,7' ( some csids ) then this statment might fail ..

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

This has got to be dynamic SQL, so you could just try:

DECLARE @sql varchar(800)

SET @sql = 'SELECT AccountId FROM Ca' + ISNULL(' WHERE CSId in (' + @GMCSids + ')','')

EXEC(@sql)

--Jonathan

tkc SSCommitted Points: 1535 More actions · Answer 4

quote:
Thanks tkc, but this returns only records with Not NULL csid records and moreover
if GMCSids = '5,6,7' ( some csids ) then this statment might fail ...

Right, I see what you need now.

I thought I could find a method that would avoid dynamic SQL, but I guess now that Jonathon is right. If your value of @GMCSids could be a varchar of values that have to be compared to an integer field using an IN() clause, then you'll probably have to go with his solution.

- Tom