Hello,
I'm struggling with the stored procedure below that uses error trapping without using TRY/CATCH. I'm trying to fix the error without resorting to TRY/CATCH. When I run the code, I receive an error message that I have not declared the scalar variables. Is there any way to detect the error without using TRY/CATCH?
CREATE PROCEDURE DeleteOrder (@OrderID int)
AS
BEGIN TRANSACTION -- Start the transaction
-- Delete the OrderDetails
DELETE FROM Sales.OrderDetails
WHERE OrderID = @OrderID
-- Delete the order
DELETE FROM Sales.Orders
WHERE OrderID = @OrderID
-- See if there is an error
IF @@ERROR <> 0
-- There's an error b/c @ERROR is not 0, rollback
ROLLBACK
ELSE
COMMIT
Thanks in advance for your help,
RM
Why are you trying to avoid TRY/CATCH? CATCH is how you intercept an error so that it doesn't bomb out.
I don't see how the code you've posted in here would throw a syntax error. Is it when you are trying to execute the stored procedure or when trying to execute what you posted?
It's when I'm trying to execute what I posted. I'm trying to avoid performance issues when used in a loop.
Thanks,
RM
Can you post the actual error you're getting, please?
Msg 2714, Level 16, State 3, Procedure DeleteOrder, Line 1
There is already an object named 'DeleteOrder' in the database.
That message is from the attempt to CREATE the Sproc I think? and not from executing it.
Either pre-delete the SProc, before the CREATE, IF it already exists, or use ALTER instead of create (and perhaps have a condition to create a one-line dummy if it does not already exist).
Benefit of ALTER:
Existing Sproc will remain if the new one fails to create (e.g. due to syntax error). Permissions are retained.
Disadvantage of ALTER:
If the script fails the original is still there. In a large script you might miss the error message from the failed create, and assume the database was fully upgraded to the "latest version".
NOTE: If you use tests on @@ERROR you need to do that after EVERY statement that might raise an error. @@ERROR is reset on EVERY SQL statement, so in your example, above, it would only check the second DELETE statement.
We do something like:
DECLARE @intError int, @intRowCount, @intReturnValue int = 0
--
DELETE FROM Sales.OrderDetails
WHERE OrderID = @OrderID
SELECT @intError = @@ERROR, @intRowCount = @@ROWCOUNT
--
IF @intError <> 0 SELECT @intReturnValue = -1
--
DELETE FROM Sales.Orders
WHERE OrderID = @OrderID
SELECT @intError = @@ERROR, @intRowCount = @@ROWCOUNT
--
IF @intError <> 0 SELECT @intReturnValue = -2
--
IF @intReturnValue <> 0
ROLLBACK
ELSE
COMMIT
--
RETURN @intReturnValue
We also test @intRowCount = 0 when we expect a DELETE to delete some rows - as deleting ZERO Rows would be a logic/data quality error of some kind.
What does TRY/CATCH have to do with performance issues?
Change your CREATE PROC to ALTER PROC since the object already exists.