This week I came across a bug in a stored procedure caused by this baffling default behaviour in SQL Server. By default, the execution of a stored procedure continues onto the next statement after an error is raised. The stored procedure in question calls another stored procedure, which has been modified in the previous release to take an extra parameter.
DECLARE @tmp TABLE (value VARCHAR(100)) INSERT INTO @tmp EXEC sp_anotherStoredProcedure @orderId = @orderId, @petType = 'cat' IF NOT EXISTS( SELECT * FROM @tmp l INNER JOIN Awesome_Event_Buffer r ON l.value = r.Description WHERE r.customer = @customer ) print 'do some important things'
Because of the addition of the new parameter, the nested store procedure call now always fail with an error. However, the calling stored procedure is not halted, but continues with the next statement. The if not exists check now always return true, and the important things are done regardless.
To abort a stored procedure whenever a runtime error occurs, set When SET XACT_ABORT to ON, like
ALTER PROCEDURE [dbo].[sp_storedProcedure] @parameter1 INT, @parameter2 INT AS set xact_abort on ...
Alternatively, you can check for errors explicitly using the error number @@error
if (@@error <> 0) return (@@error)