You develop a Microsoft SQL Server 2012 database.
You need to create a batch process that meets the following requirements:
Returns a result set based on supplied parameters.
Enables the returned result set to perform a join with a table.
Which object should you use?
A.
Inline user-defined function
B.
Stored procedure
C.
Table-valued user-defined function
D.
Scalar user-defined function
What’s wrong with answer A?
A doesn’t specify a table, which is required
Returns a result set based on supplied parameters: a FUNCTION takes a parameter, and returns a result set.
Enables the returned result set to perform a join with a table: a TABLE-VALUED FUNCTION returns its result as a table.
Correct Option: Table-valued user-defined function.
The correct answer is indeed A.
A table-valued function does not return a result set – it returns a table. See: https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx (‘No Transact-SQL statements in a table-valued function can return a result set directly to a user. The only information the function can return to the user is the table returned by the function.’)
I think that a key word is “a batch process”
Indeed. In that case C is correct, as in inline functions you can only have a single select statement, which does not allow for batch processing.
what do you mean by “does not allow for batch processing”?
Any links?