Is it possible to check the @@ rowcount function to determine if a row exists?

Question

I look through our code base and see many such tests:

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's the question, is it normal / not to replace the condition in if with:

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

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

Answer 1

Yes.

WHERE does not apply to PK (or a unique index), more than one row may be returned, but this is likely to be an error. In this case, the variable is reassigned, 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 

Change They just noticed that your proposed test is if @@rowcount = 0 not if @@rowcount <> 1 , so the above will not affect it, and the whole answer can be reduced to the word "Yes"!

Answer 2

Only for small volumes, yes

It is better to try to insert INSERT and UPDATE on error. You can assign values ​​using 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:

A few other answers from me :. Hope you get this idea.

• Only insert a row if it does not already exist
• Select / Insert Upsert version: is there a design template for concurrency?

Answer 3

Yes. This is the general picture when working with upserts .