We help IT Professionals succeed at work.

Dynamic SQL CASE STATEMENT in a text string

Lisa Callahan
on
92 Views
Last Modified: 2018-09-14
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!
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
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 CallahanBusiness Intelligence Analyst

Author

Commented:
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>
CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Lisa CallahanBusiness Intelligence Analyst

Author

Commented:
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 :)
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
You're welcome. Glad it helped :)
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.