title: How to check if a table variable is empty in SQL Server?
body: <p>This is a section of one of my stored procedure:</p>
  
  <pre><code>@dataInTable dbo.Table_Variable readonly,
  ....
  
  AND (
      ( @dataInTable IS NULL )
      OR
      ( item IN ( SELECT T FROM @dataInTable ) )
  )
  </code></pre>
  
  <p><code>@dataInTable IS NULL</code> is wrong in syntax, error is </p>
  
  <blockquote>
    <p>Must declare the scalar variable "@dataInTable"</p>
  </blockquote>
  
  <p>So I change it to:</p>
  
  <pre><code>(SELECT T FROM @dataInTable) IS NULL
  </code></pre>
  
  <p>This works but if <code>@dataInTable</code> has more than 1 item, I get an error: </p>
  
  <blockquote>
    <p>Subquery returned more than 1 value. This is not permitted when the
    subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as
    an expression.</p>
  </blockquote>
  
  <p>Understandable, so I change it to:</p>
  
  <pre><code>(SELECT TOP(1) T FROM @ProgramRatings) IS NULL
  </code></pre>
  
  <p>Works perfectly, what I have is performance concern.</p>
  
  <p>I am wondering, if there has an easier way to check whether a table variable is empty, like</p>
  
  <pre><code>AND (
      ( @dataInTable IS EMPTY )
      OR
      ( item IN ( SELECT T FROM @dataInTable ) )
  )
  </code></pre>