What should you create?

You need to create a database object that meets the following requirements:

– accepts a product identified as input

– calculates the total quantity of a specific product, including quantity on hand and quantity on order

– caches and reuses execution plans

– returns a value

– can be called from within a SELECT statement

– can be used in a JOIN clause

What should you create?
A . an extended stored procedure
B . a user-defined scalar function
C . a user-defined stored procedure that has an OUTPUT parameter
D . a temporary table that has a columnstore index

View Answer

Answer: B

Explanation:

User-defined scalar functions are execution plans that accept parameters, perform an action such as a complex calculation, and returns the result of that action as a value. The return value can either be a single scalar value or a result set. Furthermore the execution plan is cached and reusable. User-defined scalar functions can also be called from within a SELECT statement and can be used in a JOIN clause.

Incorrect Answers:

A: Using extended stored procedures is not recommended as they has been deprecated. CLR Integration should be used instead of extended stored procedures.

C: Stored procedures cannot be used in a SELECT statement or in a JOIN clause.

D: A temporary table is a result set and not a value.

References: https://www.c-sharpcorner.com/UploadFile/996353/difference-between-stored-procedure-and-user­defined-functio/