Menu
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?
908
SUM() and COUNT() functions SUM of values of a field or column of a SQL table, generated using SQL SUM() function can be stored in a variable or temporary column referred as alias. The same approach can be used with SQL COUNT() function too.
You just need parentheses around your select: SET @times = (SELECT COUNT(DidWin) FROM ...) Or you can do it like this: SELECT @times = COUNT(DidWin) FROM ...
When a variable is first declared, its value is set to NULL. To assign a value to a variable, use the SET statement. This is the preferred method of assigning a value to a variable. A variable can also have a value assigned by being referenced in the select list of a SELECT statement.
COUNT(*) returns the number of rows in a specified table, and it preserves duplicate rows. It counts each row separately. This includes rows that contain null values.
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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
