Exam 70-761 topic 1 question 194 discussion

Actual exam question from Microsoft's 70-761

Question #: 194
Topic #: 1

HOTSPOT -
You are creating a database solution to track sales achievements of your training courses. You run the following statements:

You plan to add courses to a table named HighlightedCourses. You must add courses that have been delivered to more than 100 participants only.
If the total number of participants for a course is lower than 100, the course must not be added to the HighlightedCourses table. In addition, an error message must be displayed and remaining Transact-SQL code must not run.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Show Suggested Answer

Suggested Answer: Explanation
Box 1: THROW -
TRHOW raises an exception and transfers execution to a CATCH block of a TRY...CATCH construct.
If a TRY...CATCH construct is not available, the statement batch is terminated. The line number and procedure where the exception is raised are set.
Box 2: IF (@TotalParticipants < 100)
Incorrect Answers:
Not BREAK: BREAK exits the current WHILE loop. If the current WHILE loop is nested inside another, BREAK exits only the current loop, and control is given to the next statement in the outer loop.
Not RAISERROR: Microsoft recommends that THROW should be used instead of RAISERROR.
References:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/throw-transact-sql

by szolita at Jan. 11, 2020, 4:02 p.m.

Comments

Submit Cancel

szolita

Highly Voted 5 years, 5 months ago

Sorry, but I do not see any boxes to choose from :(

upvoted 8 times

...

Billybob0604

Most Recent 4 years, 5 months ago

something like this declare @participants int = 90 begin try begin tran if @participants >= 100 print 'Let s add some courses............' commit tran if @participants < 100 raiserror ('Go to catch block to display error',16,1) end try begin catch print 'Let s print the error message............'; throw 51000, 'No insert', 1 end catch;

upvoted 1 times

...

DBA_newbie

4 years, 6 months ago

can we really use throw even though there is no ";" after the PRINT @TotalParticipants

upvoted 1 times

...

Anette

5 years ago

I found this Transact-SQL statement: declare @courseid int declare @totalparticipants int set @courseid = 1 set @totalparticipants = (select sum(cp.numparticipants) from courses_189 c inner join courseparticipants cp on c.courseid = cp.courseid where c.courseid = @courseid group by c.courseid) print @totalparticipants CHOOSE P1 - IF (@ROWCOUNT > 100) IF (@totalparticipants < 100) CHOOSE P2 - BREAK CONTINUE RAISERROR('Course is not admissible.',16,0) THROW 50000,'Course is not admissible.',0

upvoted 3 times

Anette

5 years ago

I think P1 is IF (@TotalParticipants < 100) but I am not sure for P2 It seems like Both RAISERROR and THROW fits well here. Any hint?

upvoted 2 times

Anette

5 years ago

Well, Throw terminates the statement and fits better I think.

upvoted 2 times

...

Billybob0604

4 years, 5 months ago

@ROWCOUNT does not, because you must refer to @@ROWCOUNT. It is THROW indeed, because nothing will run after THROW.

upvoted 1 times

...