How to use declare inside case?

Question

I want to combine multiple results in one row with COALESCE. But i get problem with useing variable inside CASE:

    DECLARE @combinedString VARCHAR(MAX);
    SELECT 
         CASE 
            WHEN parent.Id = child.Id THEN 
                (SET @combinedString = COALESCE(@combinedString + ', ', '') + Name 
                FROM dbo.MyChildTable WHERE Id IN (1, 2, 3, 5)
                SELECT @combinedString as RowName)  
         END
    FROM dbo.MyParentTable parent
    JOIN dbo.MyChildTable child ON child.Id= parent.Id

And error:

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'SET'.

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'FROM'.

What can be wrong?

Looks like you want GROUP_CONCAT is SQL Server. Use sth like `SELECT @combinedString = (FOR XML ...) FROM ...` — Lukasz Szozda, Dec 03 '15 at 06:45
Possible duplicate of [How to make a query with group\_concat in sql server](http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) — Lukasz Szozda, Dec 03 '15 at 06:45
If i understood correctly i gonna use ` FOR XML` insted `COALESCE`? — Kliver Max, Dec 03 '15 at 06:51
Prepare http://sqlfiddle.com, sample data and desired output — Lukasz Szozda, Dec 03 '15 at 06:52
`CASE` in T-SQL is an **expression** - it can return one of several possible atomic value - but it's **NOT** a way to control the flow of your program. You cannot write entire code blocks inside a `CASE` - it's just an expression like `a+b` ... — marc_s, Dec 03 '15 at 06:53

Kees · Answer 1 · 2015-12-03T07:23:01.373

1

Like this:

DECLARE @combinedString VARCHAR(MAX);
SELECT 
     CASE 
        WHEN parent.Id = child.Id THEN 
            SELECT @combinedString = COALESCE(@combinedString + ', ', '') + Name 
            FROM dbo.MyChildTable WHERE Id IN (1, 2, 3, 5);
            SELECT @combinedString as RowName  
     END
FROM dbo.MyParentTable parent
JOIN dbo.MyChildTable child ON child.Id= parent.Id

edited Dec 03 '15 at 07:23

answered Dec 03 '15 at 07:14

Kees

1,408
1
15
27

How to use declare inside case?

1 Answers1