Click here to Skip to main content
15,742,619 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')        
Updated 31-Jul-18 23:20pm
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 -
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

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.
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
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
Share this answer
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