sql server 2016 – Multi-Statement Table-Valued Function with a Cursor: How to Check If The Cursor is Still Open On Error?


I have a stored procedure preparing a dataset using an employee listing as base query and performing a CROSS APPLY to a MSTVF to build a small resultset per employee. In the MSTVF, a cursor is used to build the interim result set (performing complex math using running totals and case logic). MSTVFs don’t allow TRY...CATCH blocks so I can’t trap an error directly and use CURSOR_STATUS() to close and deallocate the cursor, or can I? What can I do in the declaration of the cursor or in the CATCH block of the stored procedure to check for an open cursor in the MSTVF?