Querying Data with Transact-SQL — Question 156

You are building a stored procedure named SP1 that calls a stored procedure named SP2.
SP2 calls another stored procedure named SP3 that returns a Recordset. The Recordset is stored in a temporary table.
You need to ensure that SP2 returns a text value to SP1.
What should you do?

Answer options

• A. Create a temporary table in SP2, and then insert the text value into the table.
• B. Return the text value by using the ReturnValue when SP2 is called.
• C. Add the txt value to an OUTPUT parameter of SP2.
• D. Create a table variable in SP2, and then insert the text value into the table.

Correct answer: C

Explanation

The correct answer is C because using an OUTPUT parameter allows SP2 to send a text value back to SP1 directly. Option A is incorrect as a temporary table does not facilitate passing values back directly to the calling procedure. Option B is also incorrect because ReturnValue is typically used for integer return codes, not for text values. Option D is not suitable since a table variable also does not directly return data to the caller.