SQL Server stored procedures continue execution on error

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)