CODEWITHSUNDEEP
sqlsql-serverstored-procedures
xMetalDetectorx
xMetalDetectorx

Reputation: 160

Will a stored procedure fail if one of the queries inside it fails?

Let's say I have a stored procedure with a SELECT, INSERT and UPDATE statement.

Nothing is inside a transaction block. There are no Try/Catch blocks either. I also have XACT_ABORT set to OFF.

If the INSERT fails, is there a possibility for the UPDATE to still happen?

The reason the INSERT failed is because I passed in a null value to a column which didn't allow that. I only have access to the exception the program threw which called the stored procedure, and it doesn't have any severity levels in it as far as I can see.

Upvotes: 3

Views: 7771

Answers (1)

Alan
Alan

Reputation: 3042

Potentially. It depends on the severity level of the fail.

User code errors are normally 16.

Anything over 20 is an automatic fail.

Duplicate key blocking insert would be 14 i.e. non-fatal.

Inserting a NULL into a column which does not support it - this is counted as a user code error (16) - and consequently will not cause the batch to halt. The UPDATE will go ahead.

The other major factor would be if the batch has a configuration of XACT_ABORT to ON. This will cause any failure to abort the whole batch.

Here's some further reading:

list-of-errors-and-severity-level-in-sql-server-with-catalog-view-sysmessages

exceptionerror-handling-in-sql-server

And for the XACT_ABORT

https://www.red-gate.com/simple-talk/sql/t-sql-programming/defensive-error-handling/

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql

In order to understand the outcome of any of the steps in the stored procedure, someone with appropriate permissions (e.g. an admin) will need to edit the stored proc and capture the error message. This will give feedback as to the progress of the stored proc. An unstructured error (i.e. not in try/catch) code of 0 indicates success, otherwise it will contain the error code (which I think will be 515 for NULL insertion). This is non-ideal as mentioned in the comments, as it still won't cause the batch to halt, but it will warn you that there was an issue.

The most simple example:

DECLARE @errnum AS int;
-- Run the insert code
SET @errnum = @@ERROR;
PRINT 'Error code: ' + CAST(@errornum AS VARCHAR);

Error handling can be a complicated issue; it requires significant understanding of the database structure and expected incoming data.

Options can include using an intermediate step (as mentioned by HLGEM), amending the INSERT to include ISNULL / COALESCE statements to purge nulls, checking the data on the client side to remove troublesome issues etc. If you know the number of rows you are expecting to insert, the stored proc can return SET @Rows=@@ROWCOUNT in the same way as SET @errnum = @@ERROR.

If you have no authority over the stored proc and no ability to persuade the admin to amend it ... there's not a great deal you can do.

If you have access to run your own queries directly against the database (instead of only through stored proc or views) then you might be able to infer the outcome by running your own query against the original data, performing the stored proc update, then re-running your query and looking for changes. If you have permission, you could also try querying the transaction log (fn_dblog) or the error log (sp_readerrorlog).

Upvotes: 5

Related Questions