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?
A.
An inline table-valued function
B.
A stored procedure
C.
A scalar user-defined function
D.
A multi-statement table-valued function
D
why d?
my wife asks me that all the time!
The correct answer is D because APPLY can be invoked with TVFs and inline TVFs does not allow variable declaration.
https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx (APPLY)
http://dba.stackexchange.com/questions/97336/cross-apply-on-scalar-function
http://www.sqlservercentral.com/Forums/Topic1015111-338-1.aspx
https://technet.microsoft.com/en-us/library/ms189294(v=sql.105).aspx (Inline TVF)
https://technet.microsoft.com/en-us/library/ms191165(v=sql.105).aspx (TVF)
D is wrong because table-valued functions can’t insert, delete or update permanent tables. http://www.wiseowl.co.uk/blog/s347/limitations.htm
D can’t be right because the Point “Support structured exception handling.” is not fullfilled.
May None of the Options is right!?
It is D, because A (inline function) does not support error handling (try-catch, raiserror). And the choice is between functions, because only they support APPLY
agree with Error Torsten handling is restricted in a user-defined function. A UDF does not support TRY…CATCH, @ERROR or RAISERROR. answer or question might be screwed
D is the best answer. In terms of exception handling, question it does not say it has to be TRY CATCH; I would assume If then else ..etc is what the question referring.