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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.