Click here to Skip to main content
15,042,814 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
I would like to Interpret a variable and then i like to do a insert in a MS SQL table. I tried the Code below. But i'm getting a Syntax error. I followed the Information on SQL Server: CASE WHEN OR THEN ELSE END => the OR is not supported - Stack Overflow[^]

Maybe anyone knows what happend?

What I have tried:

CASE '${state.Objektiv}'
    WHEN 'vorne rechts' THEN '2'

    WHEN 'vorne links' THEN '3'

    WHEN 'hinten rechts' THEN '4'

    WHEN 'hinten links' THEN '5'

    WHEN 'Innenraum' THEN '6'

    WHEN 'Navigationsgerät' THEN '7'

    WHEN 'Kombiinstrument' THEN '8'
    
    WHEN 'Fahrzeugdatenträger' THEN '9'
    
    WHEN 'Servicenachweis' THEN '10'
    
    ELSE '11'
END as Bildnummer

Insert into tblFoto (Bildnr) values ('$Bildnummer')        
Posted
Updated 31-Jul-18 23:20pm
v2
Comments
CHill60 1-Aug-18 5:07am
   
What is the error?
Also you are not using Bildnummer you are attempting to insert the string '$Bildnummer'
Naga Sindhura 1-Aug-18 5:27am
   
where you actually referring this column, is it in table select part?/ are you trying to store it in a variable? can you post the complete solution if possible. And, also the error details.
WHEN 'vorne rechts' THEN '2 -- what does it mean exactly. 'vorne rechts' refers to column name and 2 refers to column position?

1 solution

I'm not familiar with the format '${state.Objektiv}' - that is not how variables are used in MSSQL. See Variables (Transact-SQL) | Microsoft Docs[^].
The $ symbol isn't listed as a Symbol that would be used in MSSQL - SQL Symbol Cheat Sheet[^]

Does this example help you -
SQL
declare @tblFoto table (Bildnr int) -- You won't need this bit if you already have your table
declare @state_Objektiv varchar(255) = 'Fahrzeugdatenträger'
declare @Bildnummer int

set  @Bildnummer = (SELECT 
CASE @state_Objektiv
    WHEN 'vorne rechts' THEN 2

    WHEN 'vorne links' THEN 3

    WHEN 'hinten rechts' THEN 4

    WHEN 'hinten links' THEN 5

    WHEN 'Innenraum' THEN 6

    WHEN 'Navigationsgerät' THEN 7

    WHEN 'Kombiinstrument' THEN 8
    
    WHEN 'Fahrzeugdatenträger' THEN 9
    
    WHEN 'Servicenachweis' THEN 10
    
    ELSE 11
END)

insert into @tblFoto (Bildnr) values (@Bildnummer)
select * from @tblFoto


EDIT - @Naga-Sindhura has pointed out that that is a long list of conditions that are probably better handled using a table variable. e.g.
SQL
declare  @references table (Beschreib nvarchar(255), bildnr int)
insert into @references (Beschreib, bildnr) values
('vorne rechts',  2),
('vorne links',  3),
('hinten rechts',  4),
('hinten links',  5),
('Innenraum',  6),
('Navigationsgerät',  7),
('Kombiinstrument',  8),
('Fahrzeugdatenträger',  9),
('Servicenachweis',  10)
Note I have not added an entry for the ELSE condition (11).
Then you can do something like
SQL
declare @tblFoto table (Bildnr int)
declare @state_Objektiv varchar(255) = 'FahrzeugdatenträgerXXX'
declare @Bildnummer int = (SELECT bildnr from @references WHERE Beschreib = @state_Objektiv)
insert into @tblFoto (Bildnr) values (ISNULL(@Bildnummer,11))
select * from @tblFoto
Notice the use of ISNULL to handle what was the ELSE condition
   
v2
Comments
Naga Sindhura 1-Aug-18 5:29am
   
too many when statements, its better to go with table variable syntax. What do you think?
CHill60 1-Aug-18 6:49am
   
Yes - good idea. I'll put something into the solution
Sascha Manns 1-Aug-18 6:27am
   
That one works perfectly. Thank you very much.
CHill60 1-Aug-18 11:54am
   
Would the down-voter care to let me know where I'm wrong with my solution? I am here to learn too.

Thought not.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900