Exam 70-761 topic 1 question 182 discussion

From Microsoft. If you raise an error that has a severity level of 10 or less, which is a warning, no exception is raised. https://support.microsoft.com/en-ca/help/321903/how-to-return-errors-and-warnings-from-a-sql-server-stored-procedure-i Correct answer for first box RAISEERROR ('..', 10, 1)

As the first requirement is to produce a warning and the second is an error, the severity in RAISERROR() should be 0-10 and 11-19 respectively. Severity 20-25 is fatal and terminates the current connection.

Anette's answer is right.. Just tested it : If you uncomment the select 1/0, you see that the catch block is reached. This is the 'unexpected error' type. Throw does not work anywhere in this case drop table #test create table #test ( custid varchar(5) not null ,income money ) if object_ID('abort_test','P') IS NOT NULL drop proc abort_test go create proc abort_test as begin declare @creditlimit int = 8000 declare @errormessage varchar(1000) begin try if @creditlimit > 7000 print 'Preparing for warning in try block............' raiserror ('Warning: Credit limit is over 7,000', 10 ,1) insert into #test (custid, income) values ('A5666', 20000) --select 1/0 end try begin catch print 'Preparing for raiserror in catch block............' set @errormessage = error_message() raiserror (@errormessage, 16,1) end catch; end set xact_abort OFF; exec abort_test

For raiserror you have to generate an error of the appropriate severity, an error at severity 0 thru 10 do not cause you to jump to the catch block, so either raiserror or throw without the variable in it and with a severity level of 16 (which throw has by default) would do in the TRY block.

Since you need to raise a warning --> RAISERERROR 10-15 severity Propogate to calling code -> THROW RAISEERROR ('..', 10, 1) -> to raise the warning THROW 51000, @ErrorMessage, 1 -> to 'Propagates all unexpected errors ..

i think the answers are: 1 box: RAISEERROR ('..', 10, 1) -> to raise the warning 2 box: THROW 51000, @ErrorMessage, 1 -> to 'Propagates all unexpected errors ..'

The req "Propagates all unexpected errors to the calling process" eliminates all choices with RAISERROR() for second target because all hardcode the severity, and you should get the severity by calling ERROR_SEVERITY(). Since @ErrorMessage is defined you have to use it, so the only possibility is THROW 51000, @ErrorMessage, 1 Since THROW is used for second target you should also use THROW for the first target and the best choice is THROW 51000, 'Warning: Credit limit is over 7000', 1 so the answer should be THROW 51000, 'Warning: Credit limit is over 7000', 1 THROW 51000, @ErrorMessage, 1

Correct solution: 1. RAISERROR('Warning: Credit limit is over 7,000!', 10,1) 2. RAISERROR('Warning: Credit limit is over 7,000!', 16,1) Tested :)

<Produces a warning if the credit limit parameter is greater than 7,000> : This doesn't mean that the credit limit can't be inserted into the table, it's just a warning, therefore the 1st Statmente should be Raiserror ('Warning.... ', 10,1). Only on a runtime error the procedure have to walk into the the Catch block, log the errormessage and probagate the error status to the calling process. Just the "THROW;" without paramenters seems to be enough as second statement.