Concat mutiple SQL scalar-valued function values

Question

I have defined the following scalar-valued function:

CREATE OR ALTER FUNCTION getName
(
    @name NVARCHAR(200),
    @deletedDate DATETIME2(7) = NULL,
    @suffix NVARCHAR(50) = NULL
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN QUOTENAME(CONCAT(@name, IIF(@suffix IS NOT NULL, ' ' + @suffix, ''), IIF(@deletedDate IS NOT NULL, CONCAT(' (DELETED - ', FORMAT(@deletedDate, 'dd.MM.yyyy HH:mm:ss'), ')'), '')))
END

And what I'm trying to do is to concatenate its results in a variable as follows:

DECLARE @Columns NVARCHAR(MAX);

SELECT @Columns = COALESCE(@Columns + ',', '') + [dbo].[getName]([Name], [DeletedDate], DEFAULT)
FROM [dbo].[Items]
WHERE [ProjectId] = 1
ORDER BY [Order] DESC;

SELECT @Columns

But the @Columns result only has the last result of the function and if I remove the function and do inline computation it works as expected (having all the values).

I can't understand why is this not working when using the function.

Which dbms are you using? (The above code is product specific.) — jarlh
– jarlh, Commented Dec 7, 2022 at 14:11

Sergiu Molnar · Accepted Answer · 2022-12-07 14:24:51Z

1

Because I couldn't find any answer on the internet, If anyone else needs the answer, I've managed to resolve it with an intermediary CTE:

WITH Names_CTE
AS 
(
    SELECT   [dbo].[getName]([Name], [DeletedDate], DEFAULT) AS [Name], [Order]
    FROM     [dbo].[ITems]
    WHERE    [ProjectId] = 1
    ORDER BY [Order] DESC OFFSET 0 ROWS
)
SELECT @Columns = COALESCE(@Columns + ',', '') + [Name]
FROM Names_CTE

SELECT @Columns

I think that was not working because we needed to know the values up front to perform the computation together with COALESCE.

answered Dec 7, 2022 at 14:24

Sergiu Molnar

1,0403 gold badges16 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Concat mutiple SQL scalar-valued function values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related