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:
http://msdn.microsoft.com/en-us/library/ms190193.aspx http://msdn.microsoft.com/en-us/library/ms188790.aspx
None of the above
A stored procedure ends when the T-SQL batch ends, but you can cause the procedure to exit
at any point by using the RETURN command. You can use more than one RETURN command
in a procedure. RETURN stops the execution of the procedure and returns control back to the
caller. Statements after the RETURN statement are not executed.
RETURN by itself causes SQL Server to send a status code back to the caller. The statuses
are 0 for successful and a negative number if there is an error. However, the error numbers
are not reliable, so you should not rely on them. Use the SQL Server error numbers from
@@ERROR or from ERROR_NUMBER() in a CATCH block instead.
You can send your own return codes back to the caller by inserting an integer value after
the RETURN statement. However, if you want to send information back to the caller, it is considered
a better practice to use an OUTPUT parameter instead.
suppose I create the below SP:
CREATE proc DeleteJobCandidate
as
set NOCOUNT ON
begin try
declare @i as int
set @i=cast(‘a’ as int)
end try
begin catch
return
end catch
GO
DECLARE @ErrorVar INT;
DECLARE @RowCountVar INT;
EXEC DeleteJobCandidate
SELECT @ErrorVar = @@ERROR, @RowCountVar = @@ROWCOUNT;
if (@ErrorVar0)
PRINT N’Error = ‘ + CAST(@ErrorVar AS NVARCHAR(8)) +
N’, Rows Deleted = ‘ + CAST(@RowCountVar AS NVARCHAR(8));
GO
will display
Error = 245, Rows Deleted = 0
EXEC DeleteJobCandidate
PRINT N’Error = ‘ + CAST(@@ERROR AS NVARCHAR(8)) +
N’, Rows Deleted = ‘ + CAST(@@ROWCOUNT AS NVARCHAR(8));
will display
Error = 245, Rows Deleted = 0
?
a
ERROR_STATE() function can only be invoked inside of transaction otherwise it is null. In ‘b’ and ‘c’ this function is used independently of transaction so they are both wrong. Answer ‘d’ assumes that error occured, but in text it is said, that error should be reported only if error occured.
Everything is ok with ‘a’ answer.