CODEWITHSUNDEEP
sqlsql-serversql-server-2008
mameesh
mameesh

Reputation: 3761

Conditionally setting variable with case statement

I have the following query. I am trying to only add the values, if the value is not null so the whole variable doesnt end up as a null. Obviously this query below is erroring out, but I do not want to use an ISNULL check on the test column because it puts a comma at the end of the variable text. How would I do this without having the comma at the end of the variable text. Sometimes there will be nulls in the test column, and sometimes there will not be.

DECLARE @Test TABLE
(
    test varchar(20)
)

INSERT INTO @Test
SELECT 'adfasdfasd'
UNION ALL
SELECT NULL


DECLARE @DocID varchar(max)

SELECT CASE WHEN test IS NOT NULL THEN @DocID = COALESCE(@DocID + ',' ,'')  + test END
FROM @Test

SELECT @DocID

Upvotes: 4

Views: 10510

Answers (4)

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

You only need that pattern if you have other fields involved

SELECT @DocID = COALESCE(@DocID + ',' + test, test, @DocID),
       @otherfield ....
FROM @Test;

Otherwise, as Martin commented, removing the NULLs using WHERE makes it easy

SELECT @DocID = COALESCE(@DocID + ',', '') + test
FROM @Test
WHERE test IS NOT NULL;

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 452988

Either of the following should work

SELECT @DocID = COALESCE(@DocID + ',', '') + test
FROM   @Test
WHERE  test IS NOT NULL

or

SELECT @DocID = CASE
                  WHEN test IS NULL THEN @DocID /*No-op. */
                  ELSE COALESCE(@DocID + ',', '') + test
                END
FROM   @Test

Upvotes: 3

Kermit
Kermit

Reputation: 34055

I believe it should be:

SELECT
    @DocID = CASE
        WHEN test IS NOT NULL
            THEN COALESCE(@DocID + ',' ,'')  + test
        ELSE NULL
    END
FROM @Test

This will assign the last row's value of test to @DocID. This means that with this table:

test
--------------------
NULL
adfasdfasd

@DocId will be adfasdfasd

And with this table:

test
--------------------
adfasdfasd
NULL

@DocId will be NULL

Upvotes: 1

bummi
bummi

Reputation: 27367

DECLARE @Test TABLE
(
    test varchar(20)
)

INSERT INTO @Test
SELECT 'adfasdfasd'
UNION ALL
SELECT NULL
UNION ALL
SELECT 'Nasenbär'



DECLARE @DocID varchar(max)
Select @DocID=''
SELECT
     @DocID=@DocID + CASE
        WHEN test IS NOT NULL
            THEN Case when LEN(@DocID)>0 then ', ' else '' end + test
        ELSE ''
    END
FROM @Test

SELECT @DocID

Upvotes: 0

Related Questions