how write an Immediate If in SQL
-
I have a need to create XML tag elements as a varchar(n) variable. But, if the value is null then I should not create a tag. FOr instance, if EmployeePhone number exists I should create a tag <EmployeePhone>1234567890</EmployeePhone>. If @EmployeePhone is null then I should create no tags. Like the @EmployeePhone I have multiple variables that I need to use to create an XML String. Can someone help?
-
I guess there is no connection between my subject and message in my previous post. I need to create a T-SQL statement that works like this
iif(@EmployeePhone is NULL, NULL, '<EmployeePhone>' + @EmployeePhone + '</EmployeePhone>').
-
do something like this:
declare @employeephone char(255), @varvalue varchar(1000)
set @employeephone = '1234567890'
if @EmployeePhone is not null
begin
set @varvalue = '<EmployeePhone>' + @EmployeePhone + '</EmployeePhone>'
end
-
Thanks for the reply. My problem is that I have about 50 or 60 such tags to create and I do not want to declare so many variables and define so many if statements for every variable that I need a tag for. Is there an easier way to do that?
-
I think I found the solution! Here is what I found in the books online:
"When you concatenate null values, either the concat null yields null setting of sp_dboption or SET CONCAT_NULL_YIELDS_NULL determines the behavior when one expression is NULL. With either concat null yields null or SET CONCAT_NULL_YIELDS_NULL enabled ON, 'string' + NULL returns NULL. If either concat null yields null or SET CONCAT_NULL_YIELDS_NULL is disabled, the result is 'string'."
I can use this setting to return a null for all variables that are null and returned tagged XML elements for variables that are not null.
Thanks.
Viji
-
If in a select statement then
CASE WHEN @EmployeePhone is NULL THEN NULL ELSE '<EmployeePhone>' + @EmployeePhone + '</EmployeePhone>'
-
Check out SELECT ... FOR XML . The syntax is a little tricky, but your entire results set will be returned as an xml document, in the formatting you supply.
This will also make sure that your XML is actually conformant, and can be read by an XML reader. Otherwise, some funky characters in your data will make your XML bad.
Julian Kuiters
juliankuiters.id.au -
Be careful using CONCAT_NULL_YEILDS_NULL, especially since you'll be stringing together a bunch of these substrings, presumably to concatenate them all together into an XML doc. (see where I'm going with this?)
If one of your substrings is NULL (the way you want it), and you later concatenate that substring to another one, the resulting string will also be NULL, unless you change either the CONCAT_NULL_YEILDS_NULL setting back to OFF, or replace the NULL substring with an empty substring ('').
-
A NULL-safe variation of Antares' SQL changes the NULL to an empty string:
ISNULL(CASE WHEN @EmployeePhone is NULL THEN NULL ELSE '<EmployeePhone>' + @EmployeePhone + '</EmployeePhone>, '')
-
...now that's what I mean by being careful.
-
That's overly complicating the problem. Simply revise the original CASE suggestion (the best approach) to:
CASE WHEN @EmployeePhone is NULL THEN '' ELSE '<EmployeePhone>' + @EmployeePhone + '</EmployeePhone>' END
(Don't forget the END at the end of the CASE statement.)
Inside a select, it becomes:
SELECT /* ... above CASE statement ... */ as MyColumnName
CASE is more powerful than IIF() and offers the most direct and simple conversion of IIF()'s duties.
-
I worked around the issue by using a combination of COALESCE and NULLIF...Here is how.
@Some_String_Variable + COALESCE(NULLIF('<Telephone>' +
COALESCE(@PhoneNumber, '') + COALESCE(@PhoneExtn, '') + '</Telephone>', '<Telephone></Telephone>'), '') + @SomeOther_String_Variable
If @PhoneNumber is NULL then the COALESCE will return empty string(''), If @PhoneExtn is NULL then the COALESCE for @PhoneExtn will return NULL. That way I am only concatenating empty strings in the end and I do not have to constantly keep switching the CONCAT_NULL_YIELDS_NULL ON and OFF.
But, such a logic is extremely cumbersome, I agree. I do not know how well the Select..For XML statement will work. Will it give me the exact XML I need instead of creating its own format? If I try it with the ELEMENTS mode, but it creates elements and sub elements that does not fit the XML Structure that I require and the RAW mode provides me with a schema and XML, but I do not know how to transform the XML into the form I want inside SQL Stored Procedure...So, generating all these complex string concatenation logic seemed like the only way.
-
Ok I do think you are making more of it than you need to. Now that I understand what you need do this
@Some_String_Variable + (CASE
WHEN COALESCE(@PhoneNumber, @PhoneExtn, '') = ''
THEN ''
ELSE '<Telephone>' + ISNULL(@PhoneNumber, '') + ISNULL(@PhoneExtn, '') + '</Telephone>'
END) + @SomeOther_String_Variable
It may be a bit longer but the reability is far better. Also, as for ISNULL versus COALESCE I suggest ISNULL when you are testing only one circumstance for null to replace, if testing more than one then use COALESCE.
As you can see I used both as in your example if either @PhoneNumber or @PhoneExtn are not null then you want the XML element, if both are you want to skip the element.
As for XML with SQL itself, sorry I am still finding my way into that now and have limited knowledge of it all.
-
How about using a UDF which can be used in SQL Statement and would be much more readable
-
UDFs (a.k.a. "cursors in sheep's clothing"!) should be used with caution for any SELECT that returns more than a few results.
If SQL cannot figure how to make it "inline" -- as if you had written the code inline with your SELECT statement -- then it executes for each row in your results, dropping your performance *significantly*.
But if the SELECT is guaranteed to return a singleton, or if you write the function such that SQL can "inline" it ... then it's probably a good idea. Especially if you can write one generic function that takes the Column and proposed XML tag and does all the proper formatting.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply