CODEWITHSUNDEEP
sql-server
Ashu
Ashu

Reputation: 482

Issue in case statement

I have the following case statememt in my project.

tbl_ExceptionStatus.ExceptionStatusValue IN (case when @excstat='Awaiting ERR Approval' then ('1') else @excstat END)

Which is working fine but I have to change it to this

tbl_ExceptionStatus.ExceptionStatusValue IN (case when @excstat='Awaiting ERR Approval' then ('1','2','3') else @excstat END)

which is giving error Conversion failed when converting the varchar value '1,2,3' to data type smallint.

How am I able to change my case statememt to terminate the error?

Upvotes: 2

Views: 57

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28900

Case statement follows datatype precedence ,whatever datatype is hightest in then and else clause,it tries to convert them to that data type..See below example

declare @a int
set @a=1

select case when @a=1 then 'a' else 2 end

the above fails with

Conversion failed when converting the varchar value 'a' to data type int

in your case

then ( '1,2,3' )--varchar
 else @excstat ----int

written form of your query doesn't throw error

('1','2','3')

but this does..

'1,2,3'

Upvotes: 0

Heinzi
Heinzi

Reputation: 172270

You have a statement of the form

A IN (CASE WHEN @x='someString' THEN ('1', '2', '3') ELSE @x END)

which is not valid (you can't return a list in a case statement).

You can rewrite it as follows:

   (@x = 'someString' AND A IN ('1', '2', '3'))
OR (@x <> 'someString' AND A = @x)

If A can never be 'someString', this can be simplified to

A = @x OR (@x = 'someString' AND A IN ('1', '2', '3'))

Upvotes: 2

Related Questions