Error while using CASE in INSERT INTO statement

Question

Post reply

Error while using CASE in INSERT INTO statement

SAMDEV

SSC-Addicted

Points: 494
More actions
January 9, 2015 at 1:52 am

#313499

Hello
My below query gives me error on line where I execute first case i.e. after "WHEN 1"
=================================================
insert into AuxiliaryField2 (pageId,fieldnumber,groupid,fieldtype,textvalue,datevalue,numbervalue,memovalue,yesnovalue,blocktype)
select 1234,FieldNumber,0,Fieldtype,
case FieldType
WHEN 1
'',NULL,NULL,NULL, /* Error here: Incorrect syntax near ' */'
WHEN 2
NULL,NULL,NULL,NULL,
WHEN 4
NULL,NULL,NULL,'',
WHEN 6
'',NULL,-1,NULL,
end
0,137 FROM AuxiliaryDefs where blocktype=137 and fieldnumber > 20
===============================================================
What is going wrong?

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

The syntax for a CASE is

CASE <variable>

WHEN <expression1>

THEN <value1>

WHEN <expression2>

THEN <value2>

WHEN <expression3>

THEN <value3>

...

END

You're first missing a THEN after the expression, second you're trying to specify multiple values where only one is allowed. You'll need one CASE for each column, each returning a single value.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SAMDEV SSC-Addicted Points: 494 More actions · Answer 2

Thanks for that. But, do you mean to say that to insert values into textvalue, datevalue, numbervalue, memovalue columns, I need to repeat the case statement 4 times?

Actually,my requirement is, depending on the value of FieldType field, I need to insert different values for above mentioned four columns.

Emil B SSCertifiable Points: 5568 More actions · Answer 3

THEN can return value for one column not a set of columns like you are trying to do.

I think what you are trying to achieve is something like below:

insert into AuxiliaryField2 (pageId,fieldnumber,groupid,fieldtype,textvalue,datevalue,numbervalue,memovalue,yesnovalue,blocktype)

select

1234

,FieldNumber

,0

,Fieldtype

,CASE FieldType WHEN 1 THEN '' WHEN 2 THEN NULL WHEN 4 THEN NULL WHEN 6 THEN '' END AS [textvalue]

,CASE FieldType WHEN 1 THEN NULL WHEN 2 THEN NULL WHEN 4 THEN NULL WHEN 6 THEN NULL END AS [datevalue]

,[CASE...]

,0

,137

FROM AuxiliaryDefs

where blocktype=137 and fieldnumber > 20

SAMDEV SSC-Addicted Points: 494 More actions · Answer 4

This code gives error on line 1 as:

None of the result expressions in a CASE specification can be NULL.

I have replaced [CASE...] with proper cases but still ....

Emil B SSCertifiable Points: 5568 More actions · Answer 5

sameer.navare (1/9/2015)
This code gives error on line 1 as:
None of the result expressions in a CASE specification can be NULL.
I have replaced [CASE...] with proper cases but still ....

ohh sorry i just notieced that based on your query i returned all NULL's in second THEN, you will get this error because SQL doesn't know the data type of this column so you either replace the whole case with NULL (as anyway it will return NULL) or cast the NULL value as a datatype according to the column you want to insert to.

i.e.:

select case when 1 = 1 then NULL else NULL end; -- will fail with same error. SQL Server doesn't know the data type of the result

select case when 1 = 1 then 0 else NULL end; -- no problem, since the data type of the result is known

select case when 1 = 1 then cast(NULL as int) else null end; -- no problem, since the data type of the result is known

SAMDEV SSC-Addicted Points: 494 More actions · Answer 6

I think solution given by you as well as GilaMonster has solved the issue. I separated case statement for each of 4 columns and it worked.

Thanks all