title: CASE statement not working with "NOT LIKE" condition in T-SQL function
body: <p>What's wrong with this syntax?  And how can I fix it?</p>
  
  <p><strong>Here is the error:</strong></p>
  
  <pre><code>Msg 156, Level 15, State 1, Procedure fnTestResultStringNEW, Line 16
  Incorrect syntax near the keyword 'LIKE'.
  Msg 102, Level 15, State 1, Procedure fnTestResultStringNEW, Line 40
  Incorrect syntax near 'END'.
  </code></pre>
  
  <p><strong>Here is the T-SQL code:</strong></p>
  
  <pre><code>ALTER FUNCTION [dbo].[fnTestResultStringNEW]
  (
      @testId INT
  )
  RETURNS NVARCHAR(512)
  AS
  BEGIN
      DECLARE @totalString NVARCHAR(512)  
      SET @totalString = ''
      SELECT @totalString = 
          COALESCE(       
              CASE substance.sortorder NOT LIKE '%.0' THEN
                  CASE WHEN @totalString = '' THEN
                      testresult.result 
                  ELSE
                      @totalString + ', ' + testresult.result             
                  END
              ELSE
                  CASE WHEN @totalString = '' THEN
                      testresult.result 
                  ELSE
                      @totalString            
                  END
              END     
          , '')
      FROM [dbo].[testresult_audit] AS testresult
      JOIN [dbo].[test_testresult] 
          ON  testresult.testresultid = test_testresult.testresultid 
              AND testresult.versionnumber = test_testresult.testresultversionnumber
      LEFT OUTER JOIN substance ON substance.substanceid = testresult.substanceid
      WHERE 
      test_testresult.testid = @testid
      ORDER BY substance.sortorder, testresult.result
  
      RETURN @totalString
  END
  </code></pre>
  
  <p><strong>This works:</strong></p>
  
  <pre><code>ALTER FUNCTION [dbo].[fnTestResultStringNEW]
  (
      @testId INT
  )
  RETURNS NVARCHAR(512)
  AS
  BEGIN
      DECLARE @totalString NVARCHAR(512)  
      SET @totalString = ''
      SELECT @totalString = 
          COALESCE(       
              --CASE substance.sortorder NOT LIKE '%.0' THEN
                  CASE WHEN @totalString = '' THEN
                      testresult.result 
                  ELSE
                      @totalString + ', ' + testresult.result             
                  END
              --ELSE
              --  CASE WHEN @totalString = '' THEN
              --      testresult.result 
              --  ELSE
              --      @totalString            
              --  END
              --END       
          , '')
      FROM [dbo].[testresult_audit] AS testresult
      JOIN [dbo].[test_testresult] 
          ON  testresult.testresultid = test_testresult.testresultid 
              AND testresult.versionnumber = test_testresult.testresultversionnumber
      LEFT OUTER JOIN substance ON substance.substanceid = testresult.substanceid
      WHERE 
      test_testresult.testid = @testid
      ORDER BY substance.sortorder, testresult.result
  
      RETURN @totalString
  END
  </code></pre>