You use Microsoft SQL Server 2012 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?
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(ERRORSTATE() 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
Explanation:
Reference:
http://msdn.microsoft.com/en-us/library/ms190193.aspx
Reference:
http://msdn.microsoft.com/en-us/library/ms188790.aspx
This article explains it all:
https://technet.microsoft.com/en-us/library/ms180031(v=sql.110).aspx
One of the many reasons is that ERROR_STATE() can only be used inside a CATCH block. It will return NULL if called outside.
Answer A has a typo though.
0
0
But A is right, right? Cause it cant be B and C they never ask for Error_state just the error number
0
0
answer A has syntax error.
PRINT N’Error = ‘ + CAST(@@ErrorVar AS NVARCHAR(8)) +
N’, Rows Deleted = ‘ + CAST(@@RowCountVar AS NVARCHAR(8)); should be like below
PRINT N’Error = ‘ + CAST(@ErrorVar AS NVARCHAR(8)) +
N’, Rows Deleted = ‘ + CAST(@RowCountVar AS NVARCHAR(8));
0
0