如何在 SQL Server 中检查选择查询结果是否为 NULL [英] How to check if a select query result is NULL in SQL Server
问题描述
在 SQL Server 中,我如何验证查询是否已返回 NULL 并根据它运行块.例如在查询 1 中,我想检查 if count(*) is not null
然后检查它是否有 >0 .我应该在这里使用 if exists
吗?
in SQL Server , how do I verify if a query has returned NULL and run blocks depending on it . e.g. in query 1 , I want to check if count(*) is not null
and then check if it has >0 . Should I use if exists
here ?
if select count(*) from tbl1 not is NULL then
if select count(*) from tbl1 where count(*)>0 then
raiserror()
end if
end if
<小时>
在 Oracle 中可以说 IF INSERTING THEN
或 IF updates THEN
或 if delete then
基于列运行某个代码块.我们如何在 SQL Server 中做到这一点?请参阅下面的 Oracle 代码.
In Oracle one can say IF INSERTING THEN
or IF updating THEN
or if deleting then
run a certain block of code based on a column . how do we do it in SQL Server ? Please see Oracle code below .
CREATE OR REPLACE TRIGGER tr_name
BEFORE DELETE OR INSERT OR UPDATE OF column1 ON tbl1
FOR EACH ROW
WHEN (NEW.column1 IS NOT NULL)
begin
IF INSERTING THEN
run some code like check if there are more than row in a table and if >0 then not allow any inserts
IF updating THEN
run some code
IF deleting THEN
run some code
end
推荐答案
DECLARE @ErrorMsg nvarchar(400)
IF (SELECT count(*) FROM tbl1) = 0
BEGIN
SET @ErrorMsg = 'You are returning nothing'
SELECT @ErrorMsg Err
RETURN
END
Else IF (SELECT count(*) FROM tbl1) >= 1
BEGIN
SET @ErrorMsg = 'You are returning something'
SELECT @ErrorMsg Err
RETURN
END
你不能从计数中得到 null,所以如果你检查 0,这实际上是等价的.
You can't get null from a count so if you do a check for 0 that's practically the equivalent.
else if 检查计数返回的任何内容
The else if checks for anything that the count returns
你也可以使用 IF EXISTS
IF EXISTS (
SELECT 1 FROM tbl1
)
BEGIN
SET @ErrorMsg = 'You are returning something'
SELECT @ErrorMsg Err
RETURN
END
这篇关于如何在 SQL Server 中检查选择查询结果是否为 NULL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!