How to check if a select query result is NULL in SQL Server

Question

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

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

I really can't think of a case for this, unless you use it in a subquery and join it. Then it's no longer COUNT(*), it's just part of a recordset. — Nick.Mc
– Nick.Mc, Commented Aug 15, 2014 at 9:11
@user3844877 Maybe it would be better if you describe what you're actually trying to do. You'll get better results than with these contrived examples. — Robby Cornelissen
– Robby Cornelissen, Commented Aug 15, 2014 at 9:15
You might also want to review the MSDN page on ISNULL, because I don't think it does what you think it does, given your ISNULL(...) > 0 example in that comment. — Matthew Haugen
– Matthew Haugen, Commented Aug 15, 2014 at 9:15
Also keep in mind a query never returns NULL. A column does. A query can return no rows (in which case a COUNT would return 0) — Nick.Mc
– Nick.Mc, Commented Aug 15, 2014 at 9:19

Nick.Mc · Accepted Answer · 2014-08-16 00:34:34Z

3

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

You can't get null from a count so if you do a check for 0 that's practically the equivalent.

The else if checks for anything that the count returns

you could also use an IF EXISTS

IF EXISTS   (
        SELECT 1 FROM tbl1
)
BEGIN
    SET     @ErrorMsg = 'You are returning something'
    SELECT  @ErrorMsg Err
    RETURN 
END

edited Aug 16, 2014 at 0:34

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

answered Aug 15, 2014 at 10:08

ByronMcGrath

3752 gold badges3 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Damien_The_Unbeliever Over a year ago

You should generally use EXISTS rather than forcing the server to perform a complete count if all that you care about are 0 and at least 1.

ByronMcGrath Over a year ago

Okay I added an If exist in also :p

Nick.Mc Over a year ago

Neither of those blocks of code are correct. The first one won't even run. You'll get a syntax error. Why accept it?

Nick.Mc Over a year ago

Yes the first piece of code works now that you have edited it. The second piece of code still does not function as you describe because NOT EXISTS (SELECT count(*) FROM tbl1) is always FALSE. You might want to edit that bit now.

Nick.Mc Over a year ago

IF EXISTS (SELECT count(*) FROM tbl1) always returns true. :|. Never mind. This question is a train wreck and the OP is long gone.

|

Nick.Mc · Accepted Answer · 2014-08-15 13:21:30Z

If you are writing a trigger you generally don't bother checking if anything exists in the pseduo table, you just write the code based off the pseudo table. Also note that oracle triggers perform row by row and SQL Server triggers are called once, with possibly mutliple records in psuedo tables.

So you'd do something like this in your trigger:

INSERT INTO AnotherTable (Col1,Col2) SELECT Col1,Col3 FROM INSERTED

INSERTED is a pseudo table. It contains all records inserted (or updated). There might be 0,1, or many records in here.

If there are zero records, nothing will be inserted by this code.

Back to your original question, the best way to check if there are no rows is to do this:

IF EXISTS (SELECT 1 FROM INSERTED)
BEGIN
-- Some Code
END

Or, in a trigger, you can simply specify the trigger does not fire at all for UPDATE or INSERT.

Sunil Kumar · Accepted Answer · 2015-02-16 13:23:10Z

0

select FOUND_ROWS();

will return no. of records selected by select query.

answered Feb 16, 2015 at 13:23

Sunil Kumar

6651 gold badge8 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to check if a select query result is NULL in SQL Server

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related