Dynamic SQL CASE STATEMENT in a text string

Lisa Callahan
Lisa Callahan used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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>
Most Valuable Expert 2015
Commented:
(Updated description)

I don't think you need CASE for that.  A simple ISNULL or COALESCE should work. Try this SELECT as a test:

DECLARE @SPLC VARCHAR(100)
SET @SPLC = NULL
SELECT '<ParameterValues><ParameterValue><Name>SPLC</Name>' 
          + ISNULL('<Value>' + @SPLC+ '</Value>', '')
          + '</ParameterValue></ParameterValues>' 

Open in new window


When @SPLC is null, the resulting string is:
          <ParameterValues><ParameterValue><Name>SPLC</Name></ParameterValue></ParameterValues>

Otherwise, say @SPLC = "abc", then the resulting string is:
       <ParameterValues><ParameterValue><Name>SPLC</Name><Value>abc</Value></ParameterValue></ParameterValues>

The key is this part.  When you concatenate strings with a  NULL, the result is NULL. So you can use ISNULL to return an empty string instead.

            ISNULL('<Value>' + @SPLC+ '</Value>', '')
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 :)
Most Valuable Expert 2015

Commented:
You're welcome. Glad it helped :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial