如何在 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屋！