SELECT COUNT(*) > into variable T-SQL

Question

This should be pretty easy but for some reason I can't get the syntax right for SQL Server 2008

I need my variable to be true if a table has columns otherwise it should be false.

As of now I have this

DECLARE @LEHasSessions bit

set @LEhasSessions = ((SELECT COUNT(*) FROM @LEForSession) > 0)

which is obviously not compiling.

How should the syntax be in order to make it work?

Answer 1

SQL Server doesn't have boolean datatypes. Use CASE X THEN 1 ELSE 0 instead.

Also it is best to use EXISTS rather than COUNT here so it can stop processing after reading the first row (as opposed to counting all the rows in the table).

SET @LEHasSessions = CASE
                       WHEN EXISTS(SELECT *
                                   FROM   @LEForSession) THEN 1
                       ELSE 0
                     END 

Answer 2

You can actually just set @LEHasSessions to the output from COUNT(*). The output will be truncated down to the maximum allowable value for the destination data type. So for a BIT, even a value of 522078 will truncate down to 1 and 0 will be 0.

Using (SELECT TOP 1 * FROM @LEForSession) rather than just selecting from the table directly is necessary to satisfy performance concerns as it cuts down logical reads from scanning the entire table to scanning just one record of one page of the table.

DECLARE @LEHasSessions BIT;

SELECT @LEhasSessions = COUNT(*) 
FROM (SELECT TOP 1 * FROM @LEForSession) a
;

PRINT @LEHasSessions;