Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 100 discussion

Actual exam question from Microsoft's 70-762

Question #: 100
Topic #: 1

You have the following stored procedure that is called by other stored procedures and applications:

You need to modify the stored procedure to meet the following requirements:
✑ Always return a value to the caller.
✑ Return 0 if @Status is NULL.
✑ Callers must be able to use @Status as a variable.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

A. Replace NULL values with 0. Add a PRINT statement to return @Status.
B. Add a RETURN statement.
C. Replace NULL values with 0. Add an output parameter to return @Status.
D. Replace NULL values with 0. Add a SELECT statement to return @Status.
E. Add a PRINT statement.
F. Add a SELECT statement to return @Status.
G. Add an output parameter to return @Status.

Show Suggested Answer

Suggested Answer: BC 🗳️
There are three ways of returning data from a procedure to a calling program: result sets, output parameters, and return codes.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure?view=sql-server-2017

by JohnFan at March 22, 2020, 11:08 p.m.

Comments

Submit Cancel

caeesz

4 years, 5 months ago

I think is BD

upvoted 1 times

...

kiri2020

4 years, 7 months ago

The answer is C and it contains 2 actions - replace Null value with 0 and add an output parameter and it *doesn't* need RETURN statement. All the rest will not work because the rest option either don't replace NULL with 0 or they don't return value to the caller.

upvoted 2 times

TheDUdeu

4 years, 5 months ago

You dont need the return but it says you need to pick two answers and these are the only ones that make sense. On the test I am sure this will be just like this.

upvoted 2 times

...

Sebastian90

4 years, 7 months ago

The correct answer is C and D, you need to return @Status variable to the caller as the problem states. Using "Return Code" will return an integer type value to the caller which is not needed in this context

upvoted 1 times

TheDUdeu

4 years, 6 months ago

Why would you choose C and d it doesn't make sense the answer is correct.

upvoted 1 times

...

melvin9900

4 years, 10 months ago

I think the answer is correct . Needed two statements 1) output parameter and 2) return statement. CREATE PROCEDURE Sales.uspGetEmployeeSalesYTD @SalesPerson nvarchar(50), @SalesYTD money OUTPUT AS SET NOCOUNT ON; SELECT @SalesYTD = ISnull(SalesYTD ,0) FROM Sales.SalesPerson AS sp JOIN HumanResources.vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID WHERE LastName = @SalesPerson; RETURN

upvoted 1 times

...

Jay2

5 years ago

BG: It does not say @Status should be set to 0 if it is null. It only states that it needs to return 0 which is the default value for a successful stored procedure's return statement.

upvoted 3 times

Anette

5 years ago

I think you are wrong, it says Return 0 if @Status is NULL. I believe answer is B,C

upvoted 5 times

...

databasejamdown

4 years, 8 months ago

This is Correct. Return 0 using return statement(B) and they can access @Status as an output parameter(G)

upvoted 1 times

...

databasejamdown

4 years, 8 months ago

In retrospect, Annette is more than likely correct. Returning 0 is conditional. The return statement will always return 0 which is not what we want. I am assuming we should return 0 if status is null, otherwise return the actual status. Option c accomplishes this task.

upvoted 1 times

...

eezo

4 years, 7 months ago

I agree, BG seems to be the correct answer: When used with a stored procedure, RETURN cannot return a null value. If a procedure tries to return a null value (for example, using RETURN @status when @status is NULL), a warning message is generated and a value of 0 is returned. https://docs.microsoft.com/en-us/sql/t-sql/language-elements/return-transact-sql?view=sql-server-ver15

upvoted 3 times

...

raja1234567890

5 years ago

I think CD will be answer. You can return results using result set by using select statement and then by using output parameter

upvoted 1 times

raja1234567890

5 years ago

There is one more line. "Callers must be able to use @Status as a variable". This makes B and C as answers

upvoted 10 times

...

JohnFan

5 years, 3 months ago

1. Returning Data Using Result Sets If you include a SELECT statement in the body of a stored procedure (but not a SELECT ... INTO or INSERT ... SELECT), the rows specified by the SELECT statement will be sent directly to the client. 2.Returning Data Using an Output Parameter If you specify the OUTPUT keyword for a parameter in the procedure definition, the procedure can return the current value of the parameter to the calling program when the procedure exits. 3. Returning Data Using a Return Code A procedure can return an integer value called a return code to indicate the execution status of a procedure. You specify the return code for a procedure using the RETURN statement.

upvoted 3 times

...