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  lt  gt  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

User · Accepted Answer

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

User · Answer

Declare  MyInt int Set  MyInt     Select Count    From MyTable    If  MyInt  gt  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

User · Answer

select  myInt   COUNT    from myTable

User · Answer

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

[sql-server] Saving a select count(*) value to an integer (SQL Server)

Examples related to sql-server

Examples related to tsql

Examples related to aggregate

Examples related to variable-assignment