Is it OK to check @@rowcount function to determine existence of a row?

Question

I am going through our codebase and see a lot of tests like this:

declare @row_id int = ...
declare @row_attribute string

select
  @row_attribute = ROW_ATTRIBUTE
from
  SOME_TABLE
where
  ROW_ID = @row_id

if @row_attribute is null
begin
  ... handle not existing condition
end

Here is the question, is it OK/not OK to replace the condition in if statement to:

if @@rowcount = 0
begin
  ... handle not existing condition
end

I know about exist function, but the goal here is to get some attributes of the row and check for its existence at the same time.

Yes, but you have to keep an update lock on the table or your code is not thread safe. (which you need in both systems.) — Filip De Vos, Mar 31 '11 at 18:02
@Martin. In some cases `RAISERROR`, in others return with specific exit code, yet in others insert a row, not necessarily in the original table. — Alexander Pogrebnyak, Mar 31 '11 at 18:41

Martin Smith · Accepted Answer · 2011-03-31T18:16:49.873

6

Yes.

Except if the WHERE clause isn't on a PK (or unique index) more than one row might be returned but that would probably be an error anyway. In that eventuality the variable is repeatedly reassigned to and its final value will depend on the plan.

DECLARE @row_attribute INT

select
  @row_attribute = object_id
from
  sys.objects /*<--No WHERE clause. Undefined what the 
                   final value of @row_attribute will be.
                   depends on plan chosen  */


SELECT @row_attribute, @@ROWCOUNT

Edit. Just noticed your proposed test is if @@rowcount = 0 not if @@rowcount <> 1 so the above wouldn't affect it and the whole answer could be condensed to the word "Yes"!

edited Mar 31 '11 at 18:16

answered Mar 31 '11 at 18:00

Martin Smith

438,706
87
741
845

If I need to upsert, does select and insert/update need to be in transaction? – Alexander Pogrebnyak Mar 31 '11 at 18:44
@Alexander - Ah, in the cases you do need to upsert then I suggest gbn's approach. – Martin Smith Mar 31 '11 at 18:49

score 4 · Answer 2 · edited May 23 '17 at 12:19

4

For low volumes only, yes

You're better to try to INSERT and UPDATE on error. You can assign values with the OUTPUT clause

DECLARE @stuff TBLE (...)
BEGIN TRY
   ...
   BEGIN TRY
      INSERT table1
      OUTPUT ...
      VALUES ...()
   END TRY
   BEGIN CATCH
      IF ERROR_NUMBER = 2627
          UPDATE table1
          SET ...
          OUTPUT ...
      ELSE
         RAISERROR ...
   END CATCH
   ...
END TRY
BEGIN CATCH
   ...
END CATCH

Edit:

Several other answers from me:. Hopefully you get the idea.

edited May 23 '17 at 12:19

Community

1
1

answered Mar 31 '11 at 18:21

gbn

422,506
82
585
676

I suppose it depends on what " ... handle not existing condition" is doing. They say the goal here is to get some attributes of the row and check for its existence at the same time. Not to insert it if it doesn't exist. – Martin Smith Mar 31 '11 at 18:26

score 1 · Answer 3 · answered Mar 31 '11 at 18:02

1

Yes. This is a common pattern when dealing with upserts.

answered Mar 31 '11 at 18:02

Dave Markle

95,573
20
147
170

Is it OK to check @@rowcount function to determine existence of a row?

3 Answers3

Linked