CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
Jeff Widmer
Jeff Widmer

Reputation: 4876

What is the difference between isnull on temp table vs isnull on original table?

I am using SQL to concatenate strings together.

This statement works:

DECLARE @FirstNamesString nvarchar(256)
SELECT
    @FirstNamesString = COALESCE(@FirstNamesString + ', ', '') + p.FirstName 
FROM 
    Person p
ORDER BY
    p.SortOrder

And I get the list of first names like:

Name1, Name2, Name3

Now I want to add in a possibly null last name field for each of these persons. I tried the sql below but I only get the last item in the list (Name3):

DECLARE @FirstNamesString nvarchar(256)
SELECT @FirstNamesString = COALESCE(@FirstNamesString + ', ', '') + p.FirstName + ISNULL(' ' + p.LastName, '') 
FROM 
    Person p
ORDER BY
    p.SortOrder

BUT if I first insert all of these names into a temp table, then everything works as expected:

CREATE TABLE #Person2
(
     FirstName nvarchar(128) NOT NULL
    ,LastName nvarchar(256) NULL
    ,SortOrder int NOT NULL
)

INSERT INTO #Person2 (FirstName, LastName, SortOrder) (
    SELECT p.FirstName, p.LastName, p.SortOrder FROM Person p)

DECLARE @FirstNamesString nvarchar(256)

SELECT @FirstNamesString = COALESCE(@FirstNamesString + ', ', '') + p.FirstName + ISNULL(' ' + p.LastName, '') 
FROM 
    #Person2 p
ORDER BY
    p.SortOrder

DROP TABLE #Person2

So what is the difference between the statement above against the original table and the one against the temp table? The temp table route works and I am going to use that but I am very curious... what am I missing here?

UPDATED WITH POSSIBLE SOLUTION

See @Martin's answer but the concatenation of strings isn't guaranteed to work. So a solution is to insert the last name into the temp table first prior to selecting the list of first names:

CREATE TABLE #Person2
(
     FirstName nvarchar(256) NOT NULL
    ,SortOrder int NOT NULL
)

INSERT INTO #Person2 (FirstName, SortOrder) (
    SELECT p.FirstName + ISNULL(' ' + p.LastName, ''), p.SortOrder
    FROM Person p

)

DECLARE @FirstNamesString nvarchar(256)

SELECT
    @FirstNamesString = COALESCE(@FirstNamesString + ', ', '') + p.FirstName  
FROM 
    #Person2 p
ORDER BY
    p.SortOrder ASC

Upvotes: 1

Views: 613

Answers (2)

Martin Smith
Martin Smith

Reputation: 453786

This approach to concatenate strings isn't guaranteed to work. Microsoft say "The correct behavior for an aggregate concatenation query is undefined.". It will fail if the compute scalar ends up in the wrong place in the plan. Can you show both plans?

You could/should of course just use XML PATH to concatenate strings as you are on SQL Server 2008 and this is documented to work. reliable in current versions.

Example:

DECLARE @FirstNamesString nvarchar(256) 

SELECT @FirstNamesString = (
SELECT CASE
         WHEN ROW_NUMBER() OVER (ORDER BY (p.SortOrder)) = 1 THEN ''
         ELSE ','
       END + p.FirstName + ISNULL(' ' + p.LastName, '')
FROM   Person p
ORDER  BY p.SortOrder
FOR XML PATH(''), TYPE  

).value('.[1]','nvarchar(256)')


PRINT @FirstNamesString

Upvotes: 3

Filip De Vos
Filip De Vos

Reputation: 11908

I tried this and it works fine. But the variable engine is indeed not guaranteed to work. (and it is undocumented)

create table Person(FirstName nvarchar(10), LastName nvarchar(20), SortOrder int)

insert into Person 
VALUES ('Pers1', 'Last1', 1),
       ('Pers2', 'Last2', 2),
       ('Pers3', 'Last3', 3),
       ('Pers4', 'Last4', 4),
       ('Pers5', NULL, 5)

DECLARE @FirstNamesString nvarchar(256)
SELECT @FirstNamesString = COALESCE(@FirstNamesString + ', ', '') + p.FirstName + ISNULL(' ' + p.LastName, '') 
FROM 
    Person p
ORDER BY
    p.SortOrder

print @FirstNamesString

Upvotes: 0

Related Questions