问题描述:

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?

网友答案:

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'))
网友答案:

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' 
相关阅读:
Top