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!

_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_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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 :)

_agx_

You're welcome. Glad it helped :)