Avatar of Lisa Callahan
Lisa Callahan
Flag for United States of America asked on

Dynamic SQL CASE STATEMENT in a text string

I have created some code that will replace values using one long string. The problem is that the syntax is different if there is a NULL value in the variable.

I have the following SQL:

SET     Parameters = '<ParameterValues><ParameterValue><Name>SPLC</Name><Value>' + @SPLC + '</Value></ParameterValue></ParameterValues>'

that I want to replace with a CASE STATEMENT for the @SPLC value:

CASE WHEN @SPLC is null THEN '</ParameterValue>' ELSE '<Value>' + @SPLC + '</Value></ParameterValue>'

so the new code would look like this:

SET     Parameters = '<ParameterValues><ParameterValue><Name>SPLC</Name><Value>' + CASE WHEN @SPLC is null THEN '</ParameterValue>' ELSE '<Value>' + @SPLC + '</Value></ParameterValue>' + '</ParameterValues>'

But I can't figure it out.

Thank you!
Microsoft SQL Server

Avatar of undefined
Last Comment
_agx_

8/22/2022 - Mon
_agx_

You're missing an END for the CASE

SELECT '<ParameterValues><ParameterValue><Name>SPLC</Name><Value>' 
          + CASE  
              WHEN @SPLC is null THEN '</ParameterValue>' 
              ELSE '<Value>' + @SPLC + '</Value></ParameterValue>' + '</ParameterValues>' 
            END

Open in new window


But that produces this, which isn't valid XML (if that's what you're after)

        <ParameterValues><ParameterValue><Name>SPLC</Name><Value></ParameterValue>
Lisa Callahan

ASKER
Thank you. That does get me a bit further but you are correct in that is not what I am after. I want the string to look like this if the value is not null:

<ParameterValues><ParameterValue><Name>SPLC</Name><Value>' + @SPLC + '</Value></ParameterValue></ParameterValues>

and this if the value is null:

<ParameterValues><ParameterValue><Name>SPLC</Name></ParameterValue></ParameterValues>
ASKER CERTIFIED SOLUTION
_agx_

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Lisa Callahan

ASKER
YES!

This is exactly what I am after. Thank you for the very helpful explanation as well. I am always trying to improve my coding :)
Your help has saved me hundreds of hours of internet surfing.
fblack61
_agx_

You're welcome. Glad it helped :)