title: Conditionally setting variable with case statement
body: <p>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.</p>
  
  <pre><code>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
  </code></pre>