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

cursorsset-returning-functionssql-server-2016

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…

  1. MSFTVF takes an employee ID and pay period date ranges as params and returns a 10-column table variable

  2. 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.

  3. A cursor of qualifying transactions to wash is declared in priority order.

  4. 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.

  5. 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.

  6. 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.

  7. 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).

Best Answer

  1. Use a cursor variable (details here), then you don't have to worry about closing and deallocating (which, as David points out, isn't necessary inside a TVF, but it - and/or LOCAL - is required in other contexts). Just change:

     DECLARE c CURSOR ... FOR SELECT ...
 ...
 OPEN c;
 FETCH NEXT FROM c INTO ...

 ...

 CLOSE c;
 DEALLOCATE c;

    To:

     DECLARE @c CURSOR;
 SET @c = CURSOR READ_ONLY FORWARD_ONLY FOR SELECT ...
 ...
 OPEN @c;
 FETCH NEXT FROM @c INTO ...

 ...

  2. Work on the logic that you think requires a cursor and conditional/flow logic, so that you can eliminate the MSTVF and re-write it as an inline TVF. While it wasn't true before SQL Server 2012, I can assure you that most cursor logic in SQL Server 2016 can be rewritten using window functions. There are still isolated cases where a cursor is a better choice (not always for performance reasons), but it's doubtful that a TVF is one of them.

Related Question