IIF or CASE in concatenation query

Question

I have the following query that is part of a Scalar Variable Function but I get the same result trying to run the query so it appears that the problem is in the concatenation and/or the use of a variable.

DECLARE @useCompTitle BIT
SET @useCompTitle = (SELECT z.useComplianceTitle FROM dbo.tblConfiguration z WHERE z.id = 1)
DECLARE @Output as VARCHAR(MAX) = '';
DECLARE @ReturnValue as VARCHAR(MAX)='';
SELECT @Output = @Output + 
                '<tr><td style="vertical-align:top">' + 
                p.ProcessNumber + 
                '</td><td>' + 
                (IIF(@useCompTitle = 0, p.Process, p.title)) + 
                '</td><td style="vertical-align:top">' + 
                CASE p.recordStatus 
                    WHEN 1 THEN N'Active' 
                    WHEN 2 THEN N'Inactive' 
                END  + 
                '</td></tr>'
FROM dbo.tblProcess p
CROSS JOIN dbo.Security_Compliance_Record_List(1, 0, 0) AS p1
INNER JOIN dbo.tblERLinkedRecords l ON p.id = l.recID
WHERE l.eventID = 1172 
AND l.linkType = 'C' 
AND p.id = p1.id 
AND p.recordStatus < 3
ORDER BY p.ProcessNumber
PRINT @OutputThe result should return multiple values in @Output but I am only getting the last record in the set.

I have also tried a CASE statement with the same result. If I replace the IIF (or CASE) with a simple field name I get the correct number of values. The CASE for p.RecordStatus works correctly if the IIF is replaced by a simple field value (e.g. p.title). We use the @useCompTitle process in many Stored Procedures but this is the first time in a concatenation query.

Answer 1

Using IIF and CONCAT

A demo

declare @mytable table (processnumber int, recordstatus smallint,process varchar(20), title varchar(20))

insert into @mytable 
values 
(1,1,'Proc1','Title1'),
(2,2,'Proc2','Title2'),
(3,1,'Proc3','Title3'),
(4,2,'Proc4','Title4'),
(5,1,'Proc5','Title5')



DECLARE @useCompTitle BIT
SET @useCompTitle = 1
DECLARE @Output as VARCHAR(MAX) = '';
DECLARE @ReturnValue as VARCHAR(MAX)='';

SELECT @Output = concat(@Output, 
                '<tr><td style="vertical-align:top">', 
                p.ProcessNumber,
                '</td><td>', 
                IIF(@useCompTitle = 0, p.Process, p.title),     -- as per @useCompTitle will always display Title when 1
                '</td><td style="vertical-align:top">',
                IIF( p.recordStatus  = 1,'Active','Inactive' ),
                '</td></tr>') 
from @mytable p


print  @Output

Answer 2

Thanks for all the input. After some more testing I found that the problem was in the ORDER BY and is a known issue. That is a great article. I resolved the problem by changing the Function to as per the suggestion and it all works correctly.

  CREATE FUNCTION [dbo].[CR_Events_Linked_Compliance] 

( @parameter1 INT, @parameter2 BIT = 0, @recID INT, @useCompTitle BIT ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @Output as VARCHAR(MAX) = ''; DECLARE @ReturnValue as VARCHAR(MAX)=''; SELECT @Output = (SELECT '' + p.ProcessNumber + '' + IIF(@useCompTitle = 0, p.Process, p.title) + '' + IIF(p.recordStatus = 1, 'Active', 'Inactive') + '' FROM dbo.tblProcess p CROSS JOIN dbo.Security_Compliance_Record_List(@parameter1, @parameter2, 0) AS p1 INNER JOIN dbo.tblERLinkedRecords l ON p.id = l.recID WHERE l.eventID = @recID AND l.linkType = 'C' AND p.id = p1.id AND p.recordStatus < 3 ORDER BY p.ProcessNumber FOR XML PATH(''), TYPE ).value('.','varchar(max)') IF @Output = '' SET @ReturnValue = '' Else SET @ReturnValue = 'NumberProcessStatus' + @Output + '' RETURN @ReturnValue END

Answer 3

If a query returns one row, then SQL Server stores that value in the variable. However, if there are multiple rows, that doesn't really make sense. A variable holds a single value, but there are multiple values to store in it. It could error out, but Microsoft chose to have it simply store the last value. It could have stored the first just as well, but that's not what they decided. In general, this isn't good behavior to rely on because it's not reasonable from a semantics perspective.

In fact, I think this is where you are getting caught up. While it's possible to think of a query has being a for loop over tables and rows, it's really not. It's a series of operations on sets of tuples (rows). Conceptually, the result is a single set of rows that "arrive" at one time. There's no order of execution of the rows (any more than there's an order of execution of an array just existing). Thus, there's no sense in this.

Now, what you want to achieve is itself reasonable. You want a string that is built by essentially concatenating the results of a query that returns multiple rows. Imagine you have a simpler query where you just want to sum up some numbers instead of concatenating strings. You would just use the SUM() aggregate, potentially with a GROUP BY clause. The result of that could be stored into a variable. Since the aggregation would make it so that only one row is in the result set, you don't have to worry about which row saves the value. String aggregation works the same way. Except...SQL Server didn't have a STRING_SUM() operation until SQL Server 2017. That said, there are still ways to do this in all versions of SQL Server. You can use an XML trick, or you can write your own custom aggregate function (in a .NET language). See I need to write a query like group_concat in SQL Server for more on how to do this.

Hopefully this is enough to get you going!