帶有游標的多語句表值函式：如何檢查游標是否仍然在出錯時打開？

我有一個儲存過程，使用員工列表作為基本查詢來準備數據集，CROSS APPLY並對 MSTVF 執行以為每個員工建構一個小型結果集。在 MSTVF 中，游標用於建構臨時結果集（使用執行總計和案例邏輯執行複雜的數學運算）。MSTVF 不允許TRY...CATCH阻塞，所以我不能直接擷取錯誤並用於CURSOR_STATUS()關閉和釋放游標，或者我可以嗎？我可以在游標聲明或CATCH儲存過程塊中執行哪些操作來檢查 MSTVF 中的打開游標？

較早評論後的附加上下文（功能邏輯）

有問題的 MSTVF 程式碼建構了一組指令，以送出給計時應用程序的 API 引擎以打開計時卡、通過抵消交易調整一個或多個支付程式碼餘額並關閉計時卡。下面的基本工作流程…

0. MSFTVF 將員工 ID 和支付週期日期範圍作為參數，並返回一個 10 列的表變數
1. 變數設置為特定工資程式碼餘額的總和。這筆款項將通過 API 通過借方和貸方進行清洗。這是未結餘額。
2. 要清洗的合格交易游標按優先級順序聲明。
3. 雖然有未結餘額… 3a) 借方匹配貸方交易被插入到表變數中（用於 API 呼叫記錄） 3b) 借方/貸方的金額是目前交易或待處理餘額中的較小者（已處理通過 IF 語句） 3c) 如果交易在過去超過一個支付期，則插入另一個 API 呼叫記錄，該記錄將標記該交易以進行歷史更正。
4. 當沒有餘額時，while 循環退出 4a) 如果游標左側沒有 xaction 並且仍然有未決餘額，則插入最後一組 API 呼叫以清洗此餘額。
5. 然後查詢 API 表變數以插入用於時間卡級別的最終事務和內務呼叫（解鎖、重新總計、鎖定）的 API 呼叫記錄。如果到 4) 結束時沒有記錄存在，則不會生成任何內容。
6. API 表變數在支付程式碼和交易類型上聚合併插入到 MSTVF 表變數中並返回給呼叫過程。

使用 MSTVF 是為了封裝生成所有 API 呼叫的邏輯。為了擴展到 ILTVF，我認為在呼叫儲存過程中計算 1) 中的未決餘額後，ILTVF 將執行一個帶有 SUM() 視窗函式的查詢，以獲取執行總計。乍一看，也許可以在 CTE 中使用視窗查詢來使用多節 UNION 語句重現步驟 3) 和 4)。我認為 ILTVF 結果將儲存在呼叫過程中的臨時表中，並且步驟 5) 和 6) 執行以建構內務管理並儲存聚合結果以用於下一個 ETL 步驟（API 執行）。

1. 使用游標變數（此處有詳細資訊），那麼您不必擔心關閉和解除分配（正如 David 指出的那樣，這在 TVF 中不是必需的，但它 - 和/或LOCAL- 在其他情況下是必需的） . 只是改變：

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

...

CLOSE c;
DEALLOCATE c;

到：

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

...

2. 處理您認為需要游標和條件/流邏輯的邏輯，以便您可以消除 MSTVF 並將其重寫為內聯 TVF。雖然在 SQL Server 2012 之前並非如此，但我可以向您保證，SQL Server 2016 中的大多數游標邏輯都可以使用視窗函式重寫。在個別情況下，游標是更好的選擇（並非總是出於性能原因），但 TVF 是否是其中之一值得懷疑。

函式中只允許使用本地游標，因此無需在 catch 塊中顯式關閉或取消分配它們。

以下語句在函式中有效：

…

定義本地數據變數和本地游標的 DECLARE 語句。

…

引用在函式中聲明、打開、關閉和釋放的本地游標的游標操作。

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-ver15#interoperability

並且與許多游標選項一致，在一個函式中，無論您是否要求一個，您都會獲得一個本地游標。

本地游標不僅具有批處理級別的生命週期，而且還具有批處理級別的可見性。它們在嵌套批次中不可見，例如這個

declare c cursor local for select * from sys.objects 
open c 
exec ('close c')

失敗了

Msg 16916, Level 16, State 1, Line 28
A cursor with the name 'c' does not exist.

它們在使用 MultipleActiveResultSets 的並行批處理中也不可見。

每次創建本地游標時，它都會獲得一個唯一的 cursor_id。例如

declare c cursor local for select * from sys.objects 
open c 

exec ('declare c cursor local for select * from sys.objects;  open c;
      select cursor_id, name from sys.dm_exec_cursors(@@spid)')

輸出

cursor_id   name
----------- -----
180150091   c
180150093   c

另外像這樣的批處理或程序：

create or alter procedure ct
as
begin
   declare c cursor local for select * from sys.objects 
   open c 
   select cursor_id,name from sys.dm_exec_cursors(@@spid) 
end

可以連續多次呼叫，

create or alter procedure ct
as
begin
   declare c cursor local for select * from sys.objects 
   open c 
   select cursor_id,name from sys.dm_exec_cursors(@@spid) 
end
go

exec ct 
go 2

輸出

Beginning execution loop
cursor_id   name
----------- ------------------------------
180150115   c

(1 row affected)

cursor_id   name
----------- ------------------------------
180150117   c

(1 row affected)

或者從這樣的客戶端程序交錯並發執行：

using Microsoft.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Linq;

namespace SqlClientTest
{

   class Program
   {

       static void Main(string[] args)
       {

           var constr = @"server=localhost;database=tempdb;integrated security=true;multipleactiveresultsets= true";

           using (var con = new SqlConnection(constr))
           {
               con.Open();

               var rs = new List<SqlDataReader>();
               for (int i = 0; i < 10; i++)
               {
                   var cmd = new SqlCommand("exec ct",con);
                   rs.Add(cmd.ExecuteReader());
               }

               int rr = 0;
               foreach (var r in rs)
               {
                   while (r.Read())
                   {
                       rr++;
                       Console.WriteLine($"{r[0]} {r[1]}");
                   }
               }

               Console.WriteLine(rr);
           }
       }
   }

}

哪個輸出

180150003 c
180150005 c
180150007 c
180150009 c
180150011 c
180150013 c
180150015 c
180150017 c
180150019 c
180150021 c

引用自：https://dba.stackexchange.com/questions/274259