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?

Martin Smith · Accepted Answer · 2013-12-29 19:05:41Z

6

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

answered Dec 29, 2013 at 19:05

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bill Stidham · Accepted Answer · 2013-12-30 23:18:58Z

4

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;

edited Dec 30, 2013 at 23:18

answered Dec 29, 2013 at 19:32

Bill Stidham

1,48014 silver badges8 bronze badges

3 Comments

Bill Stidham Over a year ago

@MartinSmith the execution plan agrees with you. Both your query and mine result in a table scan (since there is no index on this memory table) however yours results in 1 logical read whereas mine results in 13 (using Production.Product from Adventure Works 2012). I've done a little reading on this COUNT vs EXISTS concept since this comment and now have this committed for future coding practices.

Bill Stidham Over a year ago

@MartinSmith I was discussing this concept with a superior colleague today and discovered another way to get the same performance (per statics io) as your "IF EXISTS" query without having to sacrifice the syntax simplicity of my "COUNT(*)" query. I've edited my answer with the new solution. This code results in only 1 logical read as it also stops at 1 record rather than reading every page in the table.

Martin Smith Over a year ago

Yes that will avoid the issue. Not sure I agree RE: Simplicity though. EXISTS seems a more natural way of expressing this to me.

juergen d · Accepted Answer · 2013-12-29 19:08:11Z

0

select @LEhasSessions = case when COUNT(*) > 0 then 1 else 0 end 
FROM @LEForSession

answered Dec 29, 2013 at 19:08

juergen d

205k40 gold badges305 silver badges377 bronze badges

Collectives™ on Stack Overflow

SELECT COUNT(*) > into variable T-SQL

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related