CODEWITHSUNDEEP
sqlsql-serverselectstored-procedures
test test
test test

Reputation: 141

why sql case condition gives error

I do something like this which works fine :

select nameOfCols 
from 
FACost
inner join  FAT as D  on d.nid=a.atypeid  
and
d.nid in (select item from SplitString('1,2,3,',','))

But when i use case to handle a situation where user dynamically may enter '' instead of '1,2,3,'. Then it gives error in my case condition

declare @selectedAssetTypeID varchar(50)='1,2,3,'
select nameOfCols 
from 
FACost
inner join  FAT as D  on d.nid=a.atypeid  
and
case when @selectedAssetTypeID<>'' then  d.nid in (select item from SplitString( @selectedAssetTypeID,',')) else d.nid=1 end     //error causing lines

errors are:

Msg 156, Level 15, State 1, Line 33
Incorrect syntax near the keyword 'in'.
Msg 156, Level 15, State 1, Line 33
Incorrect syntax near the keyword 'else'.

Upvotes: 0

Views: 52

Answers (2)

bmsqldev
bmsqldev

Reputation: 2735

you cannot use in clause with case statement. because Case has to return one value per statement (either true or false)

either you can split your queries in two blocks or you can use "OR" clause.

IF @selectedAssetTypeID = " "
BEGIN
     select nameOfCols 
     from FACost 
     inner join FAT as D  
      on (d.nid = a.atypeid)
     where d.nid = 1
END
ELSE
BEGIN
     select nameOfCols 
     from FACost 
     inner join FAT as D  
      on (d.nid = a.atypeid)
     where d.nid IN
     (select item from SplitString( @selectedAssetTypeID,','))
 END

You can also use "OR" clause

select nameOfCols 
     from FACost 
     inner join FAT as D  
      on (d.nid = a.atypeid)
     where ((@selectedAssetTypeID <>'' and  d.nid in (select item from SplitString(@selectedAssetTypeID,',')))
or (d.nid=1))

link for the discussion about the similar issue is below

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bc8a7a0b-1980-4481-a2df-6a5fde38f362/in-clause-in-case-statement?forum=sqlgetstarted

Upvotes: 1

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use and and or conditions instead of a case expression. The case expression as you have it is assigning a value (else d.nid=1) or checking for a true/false condition (d.nid in (select item from SplitString( @selectedAssetTypeID,','))).

and (
(@selectedAssetTypeID <>'' and  d.nid in (select item from SplitString( @selectedAssetTypeID,',')) )
or (d.nid=1) 
)

Upvotes: 1

Related Questions