将选择计数（*）值保存为整数（SQL Server）

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?

myTable中有记录，但是当我运行上面的代码时，print语句永远不会运行。进一步检查表明，在上面的赋值后，myInt实际上为零。我确定我错过了一些东西，但我认为选择计数会返回一个我可以在上面使用的标量？

4 个解决方案

#1

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

如果@myInt为零，则表示表中没有行：如果从未设置，则为NULL。

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

COUNT将始终返回一行，即使表中没有行也是如此。

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

编辑，2012年4月：这里的规则在我的答案中描述：COUNT（*）总是返回结果吗？

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:

但是，如果您只是寻找行的存在，（NOT）EXISTS效率更高：

IF NOT EXISTS (SELECT * FROM myTable)

#2

select @myInt = COUNT(*) from myTable

#3

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.

我不确定这是否是你的问题，但是你必须用第二个单引号来描述print语句中的单引号。虽然您可以使用SELECT来填充变量，但使用SET就像您在这里所做的那样，只需要更好，更清晰的IMO。此外，您可以保证Count（*）永远不会返回负值，因此您只需检查它是否大于零。

#4

[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.

[更新] - 嗯，我自己的愚蠢提供了这个答案。事实证明，我在运行select COUNT语句之前从myTable中删除了记录。

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.

我是怎么做到的而没有注意到的？很高兴你问。我一直在测试一个sql单元测试平台（tsqlunit，如果你感兴趣的话），作为其中一个测试的一部分，我运行了一个truncate table语句，然后是上面的。单元测试结束后，所有内容都会回滚，并且记录会返回到myTable中。这就是我在测试之外得到记录数的原因。

Sorry everyone...thanks for your help.

对不起大家...感谢您的帮助。

#1