Sql If statement based on COUNT return

Question

Basically, I am looking to modify a stored procedure that I have in my database already that updates a table's records (table x) with data coming from a web application. After updating table x, I want to develop that stored procedure to check table x to see if there exist anymore records for that instance of the primary key where column_z is null. Basically, something like

ALTER PROCEDURE [cred].[UpdateTablex]
(@field0 int,
@field1 int,
@field2 int,
@field3, int,
@field4 VARCHAR(100),
@field5 datetime,
@field6 datetime)
AS 
UPDATE tablex 
SET field4 = @field4,
field6 = @field6
WHERE field1 = @field1 AND
field2 = @field2 AND 
field3 = @field3 AND
field0 = @field0 AND  
field5 = @field5;

The rest of this will be psuedocode for my idea and the way I thought it might be developed

IF  ((SELECT COUNT(field0) FROM tablex WHERE field6 is null AND field2 = @field2
AND field3 = @field3 AND field5 = @field5) equals 0)
exec cred.Demobilize(@field0, @field1);

Or simply, if that Select statement returns any results indicating that field6 is null anywhere then we do nothing. Obviously that would mean then that if the Select statement returns nothing, then I want to execute another stored procedure.

This is probably something simple so please forgive me as I'm kinda new to certain types of SQL syntax and usage, this being one of them.

Also, could anybody point me in a proper direction to further educate myself on topics like this?

Thank you.

create a variable to store the value of the "count" results from your query. then use it in the "if" to check the condition — PiLHA
– PiLHA, Commented Jun 3, 2013 at 18:19
Ahh see that was kinda what I was thinking but I wasn't sure of the syntax there either. After a quick search: DECLARE @counter int SET @counter = SELECT.... Yes? — Jfabs
– Jfabs, Commented Jun 3, 2013 at 18:22
Right, I saw that but just failed to include it here. Thank you — Jfabs
– Jfabs, Commented Jun 3, 2013 at 18:31
Using COUNT is NOT best practice. Please use EXISTS instead. You don't need the darn count just to tell if there are any at all. — ErikE
– ErikE, Commented Jun 3, 2013 at 18:47

Gordon Linoff · Accepted Answer · 2013-06-03 18:31:56Z

6

You can do the if without using a variable:

IF  (0 = (SELECT COUNT(field0)
         FROM tablex
         WHERE field6 is null AND field2 = @field2 AND field3 = @field3 AND field5 = @field5
        )
    )
begin
    exec cred.Demobilize(@field0, @field1);
end;

In practice, I think this would more commonly be written using not exists:

IF  (not exists (SELECT 1
                 FROM tablex
                 WHERE field6 is null AND field2 = @field2 AND field3 = @field3 AND field5 = @field5
                )
    )
begin
    exec cred.Demobilize(@field0, @field1);
end;

The not exists version can be faster, because the first row encountered will satisfy the condition clause. The count(*) version has to find all matching rows and count them.

answered Jun 3, 2013 at 18:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

5 Comments

Jfabs Over a year ago

I see. I appreciate your explanation as well. This is kinda what I was hoping for. So not exists will basically just test to see if that Select statement returns any row? In this case SELECT 1 just looks for one row to be used in that test. Yes?

Gordon Linoff Over a year ago

@Jfabs . . . exactly. The exists and not exists just check for any row. The 1 is a place-holder and unimportant. In SQL Server, you can even put a nonsensical expression like 1/0 there, and no error occurs, because the actual value is not evaluated. (This is HIGHLY NOT recommended, except for demonstration purposes ;-)

ErikE Over a year ago

@GordonLinoff I was going to be a joker and say "I prefer sqrt(-2147483648) instead of 1/0 in my exists clauses" but to my surprise, it won't work--apparently it tries to resolve the constant during the parsing phase!

Gordon Linoff Over a year ago

@ErikE . . . That is amusing. It seems to try to resolve function calls but not arithmetic. Despite my reputation, I did check the 1/0 before posting and again just now, and it really does work. I think this is unfair discrimination on the part of Microsoft.

ErikE Over a year ago

Yes, it does work--I knew you were right (and checked, too, haha). Did you know that in Oracle they had to modify the engine to correctly handle SELECT 1 because best practice is SELECT * but the "SELECT 1" misinformation was cruising the internet?

Collectives™ on Stack Overflow

Sql If statement based on COUNT return

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related