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.

Answer 1

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"!

Answer 2

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.

• Only inserting a row if it's not already there
• Select / Insert version of an Upsert: is there a design pattern for high concurrency?