Solved

Simple SQL Question

Posted on 2008-06-18
4
358 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

752 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