Saving a select count(*) value to an integer (SQL Server)

Question

I'm having some trouble with this statement, owing no doubt to my ignorance of what is returned from this select statement:

declare @myInt as INT
set @myInt = (select COUNT(*) from myTable as count)

if(@myInt <> 0) 
begin
   print 'there's something in the table'
end

There are records in myTable, but when I run the code above the print statement is never run. Further checks show that myInt is in fact zero after the assignment above. I'm sure I'm missing something, but I assumed that a select count would return a scalar that I could use above?

This code runs fine for my on a couple of servers I have access to - can you elaborate on the situation that's causing this behavior? Does a regular old "SELECT COUNT(*) FROM Table" return what you expect? — SqlRyan, Mar 08 '10 at 20:12

score 58 · Accepted Answer · edited May 23 '17 at 10:30

If @myInt is zero it means no rows in the table: it would be NULL if never set at all.

COUNT will always return a row, even for no rows in a table.

Edit, Apr 2012: the rules for this are described in my answer here:Does COUNT(*) always return a result?

Your count/assign is correct but could be either way:

select @myInt = COUNT(*) from myTable
set @myInt = (select COUNT(*) from myTable)

However, if you are just looking for the existence of rows, (NOT) EXISTS is more efficient:

IF NOT EXISTS (SELECT * FROM myTable)

score 11 · Answer 2 · answered Mar 08 '10 at 16:57

11

select @myInt = COUNT(*) from myTable

answered Mar 08 '10 at 16:57

1

This is not the answer, just an alternative assignment construct. It will still give "zero" – gbn Mar 08 '10 at 18:22

score 7 · Answer 3 · answered Mar 08 '10 at 17:45

Declare @MyInt int
Set @MyInt = ( Select Count(*) From MyTable )

If @MyInt > 0
Begin
    Print 'There''s something in the table'
End

I'm not sure if this is your issue, but you have to esacpe the single quote in the print statement with a second single quote. While you can use SELECT to populate the variable, using SET as you have done here is just fine and clearer IMO. In addition, you can be guaranteed that Count(*) will never return a negative value so you need only check whether it is greater than zero.

I changed the print statement so it would make sense in my post (it had a business-specific message) and forgot to escape the quote. Thanks for your help on the select/set choice! — larryq, Mar 08 '10 at 18:17

score 2 · Answer 4 · answered Mar 09 '10 at 00:18

[update] -- Well, my own foolishness provides the answer to this one. As it turns out, I was deleting the records from myTable before running the select COUNT statement.

How did I do that and not notice? Glad you asked. I've been testing a sql unit testing platform (tsqlunit, if you're interested) and as part of one of the tests I ran a truncate table statement, then the above. After the unit test is over everything is rolled back, and records are back in myTable. That's why I got a record count outside of my tests.

Sorry everyone...thanks for your help.

Saving a select count(*) value to an integer (SQL Server)

4 Answers4

Linked