Solved

Simple SQL Question

Posted on 2008-06-18
4
351 Views
Last Modified: 2013-11-06
HI Experts

Please look at the attached code, it is an auto-generated code by MS Enterprise Library, Can you explain what is the purpose of the check for @@rowcount value and why if it is 0 , we have any concurrency problem. For me , it seems that the TRY/CATCH is enough to handle all errors and if the update have no rows to work on , @@rowcount will be 0 and it is completely OK.
Am I missing something? or I can safely remove this check and stay only with the TRY/CATCH ???

Thanks


ALTER PROCEDURE [dbo].[UpdateVmStatistics]
	@cpu_sys decimal(18,0) = NULL,
	@cpu_user decimal(18,0) = NULL,
	@elapsed_time decimal(18,0) = NULL,
	@rx_dropped_1 decimal(18,0) = NULL,
	@rx_dropped_2 decimal(18,0) = NULL,
	@rx_rate_1 decimal(18,0) = NULL,
	@rx_rate_2 decimal(18,0) = NULL,
	@tx_dropped_1 decimal(18,0) = NULL,
	@tx_dropped_2 decimal(18,0) = NULL,
	@tx_rate_1 decimal(18,0) = NULL,
	@tx_rate_2 decimal(18,0) = NULL,
	@usage_cpu_percent int = NULL,
	@usage_mem_percent int = NULL,
	@usage_network_percent int = NULL,
	@vm_guid uniqueidentifier,
	@vm_if_id_1 int = NULL,
	@vm_if_id_2 int = NULL,
	@vm_if_name_1 nvarchar(40) = NULL,
	@vm_if_name_2 nvarchar(40) = NULL,
	@vm_line_rate_1 int = NULL,
	@vm_line_rate_2 int = NULL
AS
BEGIN
	SET NOCOUNT ON
 
	BEGIN TRY
	UPDATE [dbo].[vm_statistics] 
	SET [cpu_sys] = @cpu_sys, [cpu_user] = @cpu_user, [elapsed_time] = @elapsed_time, [rx_dropped_1] = @rx_dropped_1, [rx_dropped_2] = @rx_dropped_2, [rx_rate_1] = @rx_rate_1, [rx_rate_2] = @rx_rate_2, [tx_dropped_1] = @tx_dropped_1, [tx_dropped_2] = @tx_dropped_2, [tx_rate_1] = @tx_rate_1, [tx_rate_2] = @tx_rate_2, [usage_cpu_percent] = @usage_cpu_percent, [usage_mem_percent] = @usage_mem_percent, [usage_network_percent] = @usage_network_percent, [vm_if_id_1] = @vm_if_id_1, [vm_if_id_2] = @vm_if_id_2, [vm_if_name_1] = @vm_if_name_1, [vm_if_name_2] = @vm_if_name_2, [vm_line_rate_1] = @vm_line_rate_1, [vm_line_rate_2] = @vm_line_rate_2
	WHERE [vm_guid]=@vm_guid
        /* Can you explain this ??? */
	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR('Concurrent update error. Updated aborted.', 16, 2)
	END
    END TRY
 
    BEGIN CATCH
		EXEC RethrowError;
	END CATCH	
 
	SET NOCOUNT OFF
END

Open in new window

0
Comment
Question by:elimesika
4 Comments
 
LVL 13

Accepted Solution

by:
Wizilling earned 167 total points
ID: 21818089
Basically , if you update something and you get 0 rows updates it is not considered an error. so even if u wuold just have used a try / catch block, a update of 0 records would mean that 0 rows updated successfully. .This is not an error... The reason to have a if rowcount =0 means since 0 rows was update, treat this as a error and raise a custom error ...

0
 
LVL 2

Assisted Solution

by:TheLastStraw
TheLastStraw earned 166 total points
ID: 21818138
The @@ROWCOUNT will return 0 if no rows are affected by preceding update based on filter conditions but does not raise an exception.  In the procedure the logic appears to be to raise an exception if 0 rows are updated.

The Try/Catch will trap errors if you had something like data conversion error based on parameters passed.

I would leave as is.
0
 
LVL 44

Assisted Solution

by:Arthur_Wood
Arthur_Wood earned 167 total points
ID: 21818447
The TRY...CATCH block is there to guard against an execution error.  Returning 0 records from the Update is not an execution error, but probably indicates some other kind of significant error in your database - you attempted to update at least one record, which then did not update for some reason - possibly the WHERE clause did not successfully identify an existing record in the table (which your code apparently expected to find).

I also would leave the Stored Procedure precisely as it is.  Eliminating the test on @@ROWCOUNT could end up masking problems in your database, which are not sufficient to cause a run-time error, but are symptomatic of significant data-related problems that you may need to investigate.

AW
0
 
LVL 19

Author Closing Comment

by:elimesika
ID: 31468601
Thanks for your answers, I have splited the points between all 3 answers I got.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Read about achieving the basic levels of HRIS security in the workplace.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question