title: Multiple CASE in a stored procedure
body: <pre><code>CREATE PROCEDURE [dbo].[PROC_VuReg]   
       @PACKAGEDESCR VARCHAR(50),
       @VOUCHERFROMDT VARCHAR(20),
       @VOUCHERTODT VARCHAR(20),
       @STATUS VARCHAR(10),
       @RefNo VARCHAR(10)
  AS
  BEGIN
      SET @sQRY = 'SELECT * FROM VuReg WHERE PACKAGEDESCR = ''' + @PACKAGEDESCR  +
                   ''' AND VOUCHERDT BETWEEN ''' + @VOUCHERFROMDT + ''' AND ''' +  @VOUCHERTODT + 
                   ''' AND SUBACTIVITY = CASE WHEN LEN(''' + @RefNo + ''') &gt; 0 THEN ''' + @RefNo + ''' ELSE SUBACTIVITY END AND WDNO = CASE WHEN ''' + @STATUS  + ''' =''NULL'' THEN IS NULL WHEN '''+ @STATUS   + ''' =  ''NOT NULL'' THEN  IS NOT NULL WHEN '''+ @STATUS   + ''' =  '''' THEN  WDNO END '
  
     --print @sqry
     EXEC sp_executesql @sqry
  END
  </code></pre>
  
  <p>In the above procedure, everything has worked perfectly before adding the parameter <code>@Status</code>.</p>
  
  <p>If <code>@status</code> is <code>NULL</code>, then the condition will be </p>
  
  <pre><code>SELECT * FROM VuReg WHERE WDNO IS NULL
  </code></pre>
  
  <p>If <code>@status</code> is <code>NOT NULL</code>, then the condition will be </p>
  
  <pre><code>SELECT * FROM VuReg WHERE WDNO IS NOT NULL
  </code></pre>
  
  <p>And if <code>@status</code> is <code>''</code>, then the condition will be  </p>
  
  <pre><code>SELECT * FROM VuReg WHERE WDNO = WDNO
  </code></pre>
  
  <p>{expect all the record}</p>