IF ELSE IF ELSE IF.... Code wants to get smarter

Question

I tried a lot to get this code smarter... as you see... it's always the same... one variable can have 6 different values... which decides in which column some other value will be written... if the recordset still exists I make an "update..." else I make an "insert into..."

I tried to do some dynamic sql... but it doesn't work...

I tried to do some "CASE..." code... but it doesn't work...

This code works:

-- How many roads must a man walk down...
IF @DatenFeld = 'weaNr'
    IF NOT EXISTS(SELECT * FROM @StaDa_Table WHERE DatenSatz=@DatenSatz)
        INSERT INTO @StaDa_Table (DatenSatz, weaNr ) VALUES (@DatenSatz, Convert(nvarchar(20),@DatenWert))
    ELSE
        UPDATE @StaDa_Table SET weaNr = Convert(nvarchar(20),@DatenWert) WHERE DatenSatz=@DatenSatz
ELSE
    IF @DatenFeld = 'weaTyp'
        IF NOT EXISTS(SELECT * FROM @StaDa_Table WHERE DatenSatz=@DatenSatz)
            INSERT INTO @StaDa_Table (DatenSatz, weaTyp ) VALUES (@DatenSatz, Convert(nvarchar(20),@DatenWert))
        ELSE
            UPDATE @StaDa_Table SET weaTyp = Convert(nvarchar(20),@DatenWert) WHERE DatenSatz=@DatenSatz
    ELSE    
        IF @DatenFeld = 'nennP_W'
            IF NOT EXISTS(SELECT * FROM @StaDa_Table WHERE DatenSatz=@DatenSatz)
                INSERT INTO @StaDa_Table (DatenSatz, nennP_W ) VALUES (@DatenSatz, Convert(int,@DatenWert))
            ELSE
                UPDATE @StaDa_Table SET nennP_W = Convert(int,@DatenWert) WHERE DatenSatz=@DatenSatz
        ELSE
            IF @DatenFeld = 'refErt_Wh'
                IF NOT EXISTS(SELECT * FROM @StaDa_Table WHERE DatenSatz=@DatenSatz)
                    INSERT INTO @StaDa_Table (DatenSatz, refErt_Wh ) VALUES (@DatenSatz, Convert(bigint,@DatenWert))
                ELSE
                    UPDATE @StaDa_Table SET refErt_Wh = Convert(bigint,@DatenWert) WHERE DatenSatz=@DatenSatz
            ELSE
                IF @DatenFeld = 'inbetrieb'
                    IF NOT EXISTS(SELECT * FROM @StaDa_Table WHERE DatenSatz=@DatenSatz)
                        INSERT INTO @StaDa_Table (DatenSatz, inbetrieb ) VALUES (@DatenSatz, Convert(datetime,@DatenWert))
                    ELSE
                        UPDATE @StaDa_Table SET inbetrieb = Convert(datetime,@DatenWert) WHERE DatenSatz=@DatenSatz
                ELSE
                    IF @DatenFeld = 'uw'
                        IF NOT EXISTS(SELECT * FROM @StaDa_Table WHERE DatenSatz=@DatenSatz)
                            INSERT INTO @StaDa_Table (DatenSatz, uw ) VALUES (@DatenSatz, Convert(nvarchar(50),@DatenWert))
                        ELSE
                            UPDATE @StaDa_Table SET uw = Convert(nvarchar(50),@DatenWert) WHERE DatenSatz=@DatenSatz

... so maybe someone which ist more familiar with tsql can help me to get this code smarter?

Answer 1

Maybe something like this helps:

declare @weaNr nvarchar(20) = null
declare @weaTyp nvarchar(20) = null
declare @nennP_W int = null
declare @refErt_Wh bigint = null
declare @inbetrieb datetime = null
declare @uw nvarchar(50) = null

select  @weaNr = case when @DatenFeld = 'weaNr' then Convert(nvarchar(20),@DatenWert) end
        ,@weaTyp = case when @DatenFeld = 'weaTyp' then Convert(nvarchar(20),@DatenWert) end
        ,@nennP_W = case when @DatenFeld = 'nennP_W' then Convert(int,@DatenWert) end
        ,@refErt_Wh = case when @DatenFeld = 'refErt_Wh' then Convert(bigint,@DatenWert) end
        ,@inbetrieb = case when @DatenFeld = 'inbetrieb' then Convert(datetime,@DatenWert) end
        ,@uw = case when @DatenFeld = 'uw' then Convert(nvarchar(50),@DatenWert) end

IF NOT EXISTS(SELECT * FROM @StaDa_Table WHERE DatenSatz=@DatenSatz)
    INSERT INTO @StaDa_Table (DatenSatz, weaNr, weaTyp, nennP_W, refErt_Wh, inbetrieb, uw ) 
    VALUES (@DatenSatz, @weaNr, @weaTyp, @nennP_W, @refErt_Wh, @inbetrieb, @uw)
ELSE
    UPDATE  @StaDa_Table
    SET     weaNr = ISNULL(@weaNr, weaNr)
            ,weaTyp = ISNULL(@weaTyp, weaTyp)
            ,nennP_W = ISNULL(@nennP_W, nennP_W)
            ,refErt_Wh = ISNULL(@refErt_Wh, refErt_Wh)
            ,inbetrieb = ISNULL(@inbetrieb, inbetrieb)
            ,uw = ISNULL(@uw, uw)
    WHERE DatenSatz=@DatenSatz

Assuming that all columns are nullable.