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
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-userdefined-functio/