I'm trying to do a simple concatenation but failing somewhere! I want to display the Note in the email only if the other two variables are not empty. Even if one variable contains data it should display the Note. Below is my code.
SET @Note = CASE
WHEN @tableHTML_AssignedTo IS NOT NULL OR @tableHTML_SubmittedBy IS NOT NULL
THEN 'Note: The tickets with the following statuses are not listed in this alert: Closed,Rejected'
SET @ComposeBody = ISNULL(@tableHTML_AssignedTo, '') +
ISNULL(@tableHTML_SubmittedBy, '') +
I'd do it like this -- seems simpler.
The trick here is to understand how null works with concat. Anything concat with null is null. So the first parameter to COALESCE will be null if Assigned or submitted is null. Each next step checks the other two cases.
SET @Note = 'Note: The tickets with the following statuses are not listed in this alert: Closed,Rejected'; SET @ComposeBody = COALESCE( @tableHTML_AssignedTo+@tableHTML_SubmittedBy+@Note, -- Selected if both non null @tableHTML_AssignedTo+@Note, -- Selected if SubmittedBy null @tableHTML_SubmittedBy+@Note) -- Selected if AssignedTo null -- Both are null, set body to null.