Developing Microsoft SQL Server Databases — Question 20

You need to encapsulate a T-SQL script into a reusable user-defined object.
The object must meet the following requirements:
✑ Permit insertions into a table variable.
✑ Support structured exception handling.
✑ Prevent changes to the definition of referenced objects.
✑ Support the use of the APPLY operator on the output of the object.
Which type of object should you use?

Answer options

• A. An inline table-valued function
• B. A stored procedure
• C. A scalar user-defined function
• D. A multi-statement table-valued function

Correct answer: C

Explanation

The correct answer is C, a scalar user-defined function, because it meets all the specified requirements. It allows inserts into a table variable, supports structured exception handling, prevents changes to the definitions of referenced objects, and can work with the APPLY operator. The other options do not meet all these criteria; for example, a stored procedure does not return a scalar value and is not designed for such encapsulation.