Querying Microsoft SQL Server 2012/2014 — Question 11

You use Microsoft SQL Server to develop a database application.
You create a stored procedure named DeleteJobCandidate.
You need to ensure that if DeleteJobCandidate encounters an error, the execution of the stored procedure reports the error number.
Which Transact-SQL statement should you use?

Answer options

• A. DECLARE @ErrorVar INT; DECLARE @RowCountVar INT; EXEC DeleteJobCandidate SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT; IF (@ErrorVar <> 0) PRINT N'Error = ' + CAST(@ErrorVar AS NVARCHAR(8)) + N', Rows Deleted = ' + CAST(@RowCountVar AS NVARCHAR(8)); GO
• B. DECLARE @ErrorVar INT; DECLARE @RowCountVar INT; EXEC DeleteJobCandidate SELECT @ErrorVar = ERROR_STATE(), @RowCountVar = @@ROWCOUNT; IF (@ErrorVar <> 0) PRINT N'Error = ' + CAST(ERROR_STATE() AS NVARCHAR(8)) + N', Rows Deleted = ' + CAST(@RowCountVar AS NVARCHAR(8)); GO
• C. EXEC DeleteJobCandidate IF (ERROR_STATE() != 0) PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) + N', Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8)); GO
• D. EXEC DeleteJobCandidate PRINT N'Error = ' + CAST(@@ERROR AS NVARCHAR(8)) + N', Rows Deleted = ' + CAST(@@ROWCOUNT AS NVARCHAR(8)); GO

Correct answer: A

Explanation

The correct answer is A because it correctly captures the error number using @@ERROR immediately after executing the stored procedure, allowing for proper error handling. Options B, C, and D do not utilize @@ERROR appropriately after the procedure execution, and B uses ERROR_STATE(), which does not provide the same information as @@ERROR. Hence, they do not meet the requirement of reporting the error number effectively.