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?
Additional Context after Earlier Comments (Functional Logic)
The MSTVF code in question builds a set of instructions to submit to a timekeeping app's API engine to open a timecard, adjust one or more paycode balances with offsetting transactions and close a timecard. Basic workflow below…
MSFTVF takes an employee ID and pay period date ranges as params and returns a 10-column table variable
A variable is set with a sum of specific paycode balances. This sum is to be washed via debits and credits via the API. This is the pending balance.
A cursor of qualifying transactions to wash is declared in priority order.
While there is a pending balance…
3a) A debit matching credit transaction is inserted into a table variable (for the API call records)
3b) The amount of the debit/credit is the smaller of the current transaction or pending balance (handled via IF statement)
3c) If the transaction is more than one pay period in the past, another API call record is inserted that would flag the transaction for a historical correction.
When no balance remains, the while loop exits out
4a) If there are no xactions in the cursor left and still there is a pending balance, a final set of API calls are inserted to wash this balance.
The API table variable is then queried to insert API call records for final transactions at the timecard level and housekeeping calls (unlock, retotal, lock). If no records are present by the end of 4), nothing is generated.
The API table variable is aggegrated on paycode and transaction type and inserted into the MSTVF table variable and returned back to the calling procedure.
The use of a MSTVF was to encapsulate the logic generating all the API calls. To scale to an ILTVF, I figure after calculating the pending balances in 1) in the calling stored procedure, the ILTVF would run a query with a windowing function for SUM() for the running totals. On first blush, maybe the windowing query could be used in a CTE to reproduce steps 3) and 4) with a multi-section UNION statement. I figure the ILTVF result would be stored in a temp table in the calling procedure and steps 5) and 6) run to build in the housekeeping and store the aggregated result for the next ETL step (API execution).