How to interpret a variable

Question

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 31-Jul-18 22:52pm

Sascha Manns

Updated 31-Jul-18 23:20pm

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2018-07-31T23:21:00

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