SQL 简单检查 SELECT 查询空结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2884996/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Simple check for SELECT query empty result
提问by Denys S.
Can anyone point out how to check if a select query returns non empty result set?
谁能指出如何检查选择查询是否返回非空结果集？
For example I have next query:
例如我有下一个查询：
SELECT * FROM service s WHERE s.service_id = ?;
Should I do something like next:
我应该做下这样的事情：
ISNULL(SELECT * FROM service s WHERE s.service_id = ?)
to test if result set is not empty?
测试结果集是否不为空？
回答by Ed B
IF EXISTS(SELECT * FROM service s WHERE s.service_id = ?)
BEGIN
--DO STUFF HERE
END
回答by marc_s
Use @@ROWCOUNT:
使用@@ROWCOUNT：
SELECT * FROM service s WHERE s.service_id = ?;
IF @@ROWCOUNT > 0
-- do stuff here.....
According to SQL Server Books Online:
Returns the number of rows affected by the last statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG.
返回受最后一条语句影响的行数。如果行数超过 20 亿，则使用 ROWCOUNT_BIG。
回答by Raja
I agree with Ed B. You should use EXISTS method but a more efficient way to do this is:
我同意 Ed B. 你应该使用 EXISTS 方法，但更有效的方法是：
IF EXISTS(SELECT 1 FROM service s WHERE s.service_id = ?)
BEGIN
--DO STUFF HERE
END
HTH
HTH
回答by Samim Hussain
You can do it in a number of ways.
您可以通过多种方式做到这一点。
IF EXISTS(select * from ....)
begin
-- select * from ....
end
else
-- do something
Or you can use IF NOT EXISTS , @@ROW_COUNTlike
或者你可以使用IF NOT EXISTS , @@ROW_COUNT像
select * from ....
IF(@@ROW_COUNT>0)
begin
-- do something
end
回答by KM.
try:
尝试：
SELECT * FROM service s WHERE s.service_id = ?;
IF @@ROWCOUNT=0
BEGIN
PRINT 'no rows!'
END
回答by ovais.tariq
SELECT COUNT(1) FROM service s WHERE s.service_id = ?
回答by Viranja kaushalya
SELECT * FROM service s WHERE s.service_id = ?;
IF @@rowcount = 0
begin
select 'no data'
end
回答by Donnie
To summarize the below posts a bit:
总结一下以下帖子：
If all you care about is if at least one matching row is in the DB then use existsas it is the most efficient way of checking this: it will return true as soon as it finds at least one matching row whereas count, etc will find all matching rows.
如果您只关心数据库中是否至少有一个匹配的行，那么请使用exists它，因为这是检查这一点的最有效方法：一旦找到至少一个匹配的行count，它将返回 true 而, etc 将找到所有匹配行。
If you actually need to use the data for processing or if the query has side effects, or if you need to know the actual total number of rows then checking the ROWCOUNTor countis probably the best way on hand.
如果您确实需要使用数据进行处理，或者如果查询有副作用，或者如果您需要知道实际的总行数，那么检查ROWCOUNT或count可能是手头最好的方法。
回答by tpdi
SELECT count(*) as count FROM service s WHERE s.service_id = ?;
test if count == 0 .
测试是否计数 == 0 。
More baroquely:
更巴洛克：
select case when (SELECT count(*) as count FROM service s WHERE s.service_id = ?) = 0 then 'No rows, bro!' else 'You got data!" end as stupid_message;
select case when (SELECT count(*) as count FROM service s WHERE s.service_id = ?) = 0 then 'No rows, bro!' 否则'你得到了数据！” 以愚蠢的消息结尾；
回答by csandreas1
SELECT count(*) as CountThis ....
SELECT count(*) as CountThis ....
Then you can compare it as string like so:
然后你可以像这样将它作为字符串进行比较：
IF CHECKROW_RS("CountThis")="0" THEN ...
CHECKROW_RSis an object
CHECKROW_RS是一个对象
