title: Using SELECT subquery in a CASE statement and handling NULLS
body: <p>I have 2 SQL tables one holding stock information then a style table which details how the stock is used. For some jobs the information is contained with a print file so the value in the stock table would be BIN, if not it would contain a template name which would cause a query to a <code>Styles</code> table which then shows how the stock is used. There a 10 positions that a stock could be applied but the template table wouldn't have records for where a stock couldn't be used (on the back of a simplex job for example).</p>
  <p>I have a stored procedure which works to provide the detail based on whether the logic value is BIN or not but for the records in the style table that have no value I get NULL back which I need to suppress but just putting <code>ISNULL</code> around the column name in the subquery isn't having any effect. Am I just missing something as I'd rather not nest in another <code>CASE</code> statement to check if the query would produce <code>NULL</code></p>
  <pre><code>WITH STYLES AS 
  (
      SELECT 
          @JOBNAME AS JobName, Logic AS StyleName 
      FROM 
          [dbo].[CON_Tbl_511_DigitalStocks]
      WHERE  
          JOBNAME = @JOBNAME
  )
  SELECT TOP 1 
      [Logic], 
      [S1F], [S1B], [S2F], [S2B], 
      [S3F], [S3B], [S4F], [S4B],
      [S5F], [S5B],
      CASE 
          WHEN b.stylename = 'BIN' 
              THEN --checks if there is a style for this job
                  CASE 
                      WHEN S1F = '' THEN '' 
                      ELSE '1' 
                  END -- if a stock code is specified then return the bin name (&quot;1&quot;)
              ELSE (SELECT PAGE 
                    FROM [dbo].[CON_Tbl_512_DigitalLogic] 
                    WHERE STYLENAME = B.StyleName 
                      AND STOCKREF = 'S1F') 
      END AS S1F_LOGIC,    -- If a style is used return the style instruction for this bin and side 
      CASE 
          WHEN b.stylename = 'BIN' -- repeat this for all bins/sides
              THEN 
                  CASE WHEN S1B = '' THEN ''
                  ELSE '1' 
              END 
              ELSE (SELECT PAGE 
                    FROM [dbo].[CON_Tbl_512_DigitalLogic] 
                    WHERE STYLENAME = B.StyleName AND STOCKREF = 'S1B') 
      END AS S1B_LOGIC,
      CASE WHEN b.stylename = 'BIN' THEN 
              CASE WHEN S2F = '' THEN ''
                  ELSE '2' END 
              ELSE  (SELECT PAGE FROM [dbo].[CON_Tbl_512_DigitalLogic] WHERE STYLENAME = B.StyleName AND STOCKREF = 'S2F') END AS S2F_LOGIC -- this one returns NULL as there is no instruction required for &quot;2SF&quot;
  FROM 
      [CON_Tbl_511_DigitalStocks] A 
  JOIN 
      STYLES B ON A.JOBNAME = B.JOBNAME
  WHERE 
      A.JobName = @JobName 
  </code></pre>
  <p>This code works fine until the last one as there is no value in the stockref column that says 'S2F' but putting <code>SELECT ISNULL(PAGE, '')</code> still returns NULL</p>