How to check for errors in Updates in stored procedure?

How can tell if all the UPDATES completed, and there were no errors :

alter PROCEDURE UpdateCatalog
AS
BEGIN
      SET NOCOUNT ON;
            
UPDATE Nop_Product  ...

UPDATE Nop_Product  ...

UPDATE Nop_Product  ...

-- a lot more UPDATE's

Return 555

END


If all the UPDATES completed without errors,  will it Return that last line "Return 555" statement at the end?

Right now, I'm checking the Return value in the code :

............
SqlParameter paramReturnValue = new SqlParameter();
                paramReturnValue.ParameterName = "@RETURN_VALUE";
                paramReturnValue.SqlDbType = SqlDbType.Int;
                paramReturnValue.Direction = ParameterDirection.ReturnValue;
                command.Parameters.Add(paramReturnValue);

                conn.Open();
                command.ExecuteNonQuery();
                conn.Close();
                int returnValue = (int)command.Parameters["@RETURN_VALUE"].Value;
                if (returnValue == 555)
                {
                    lbl.Text = "completed"
                }

Is this ok? thanks

LVL 16
MikeMCSDAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to check for errors, for example like this:
alter PROCEDURE UpdateCatalog
AS
BEGIN
SET NOCOUNT ON;
DECLARE @result int

SET @result = 0
BEGIN TRANSACTION
            
UPDATE Nop_Product  ...
if @@error <> 0 GOTO done

UPDATE Nop_Product  ...
if @@error <> 0 GOTO done
UPDATE Nop_Product  ...
if @@error <> 0 GOTO done
-- a lot more UPDATE's
set @result = 555


done:
IF @result = 0
  ROLLBACK
ELSE
  COMMIT

Return @result

END

Open in new window

0
 
Anthony PerkinsCommented:
I would recommend you implement a TRY ... CATCH structure that way you can report back the errors:
http://msdn.microsoft.com/en-us/library/ms179296.aspx
http://msdn.microsoft.com/en-us/library/ms175976.aspx

Also BEGIN TRANSACTION ... ROLLBACK /COMMIT TRANSACTION would be useful so that updates can be rolled back when there is an error:
http://msdn.microsoft.com/en-us/library/ms188929.aspx
http://msdn.microsoft.com/en-us/library/ms181299.aspx
http://msdn.microsoft.com/en-us/library/ms190295.aspx
0
 
SAMIR BHOGAYTAFreelancer and IT ConsultantCommented:
Yes, it is correct answer, with the help of you procedure it affected on that no. of rows which this procedure returns.
0
 
MikeMCSDAuthor Commented:
thanks all . .
angel I'm getting this error message when testing for an error (I added 'x' to the table name) :

"Invalid object name 'Nop_Productx'.
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
A transaction that was started in a MARS batch is still active at the end of the batch. The transaction is rolled back. "

Should I take out the first "BEGIN" :


alter PROCEDURE UpdateCatalog
AS

BEGIN

SET NOCOUNT ON;
DECLARE @result int
SET @result = 0

BEGIN TRANSACTION
		
UPDATE Nop_Product
SET [Name2] = Nop_Product.[Name] + ' ' + SKU
FROM Nop_Product
INNER JOIN [Nop_ProductVariant] ON [Nop_ProductVariant].[ProductID] = Nop_Product.[ProductID] 
if @@error <> 0 GOTO done
set @result = 555

done:
IF @result = 0
  ROLLBACK
ELSE
  COMMIT

Return @result

END

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>"Invalid object name 'Nop_Productx'.
the issue is that this is a compile error, and not a run-time error.
so, this kind of error should never occur in your procedure.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.