Difficulty Detecting Error without TRY/CATCH

Transact-SQL
1

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

2

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?

3

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

4

Can you post the actual error you're getting, please?

5

Msg 2714, Level 16, State 3, Procedure DeleteOrder, Line 1
There is already an object named 'DeleteOrder' in the database.

6

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.

7

What does TRY/CATCH have to do with performance issues?

8

Change your CREATE PROC to ALTER PROC since the object already exists.