下面是我的存盤程序，它有 2 個游標將資料插入同一個表中。兩個游標中有不同的邏輯，我已經為這個問題洗掉了。如何獲取在整個操作程序中受影響或插入的總行數？
ALTER PROCEDURE [dbo].[Myproc]
AS
DECLARE @fetch_status AS INT
DECLARE mycur CURSOR read_only FOR
SELECT * FROM tableName
OPEN mycur
FETCH next FROM mycur INTO @Name, @Age
SELECT @fetch_status = @@FETCH_STATUS
WHILE @fetch_status = 0
BEGIN
SET nocount ON;
INSERT INTO [dbo].[tableA] ([Name], [Age])
VALUES (@Name, @Age)
FETCH next FROM mycur INTO @Name, @Age
SELECT @fetch_status = @@FETCH_STATUS
END
CLOSE mycur
DEALLOCATE mycur
DECLARE mycur CURSOR read_only FOR
SELECT * FROM tablename
OPEN mycur
FETCH next FROM mycur INTO @Name, @Age
SELECT @fetch_status = @@FETCH_STATUS
RAISERROR ('Starting second', 10, 0) WITH nowait
DECLARE @msg AS NVARCHAR(50)
WHILE @fetch_status = 0
BEGIN
SET nocount ON;
INSERT INTO [dbo].[tableA] ([Name], [Age])
VALUES (@Name, @Age)
FETCH next FROM mycur INTO @Name, @Age
SELECT @fetch_status = @@FETCH_STATUS
END
CLOSE mycur
DEALLOCATE mycur
uj5u.com熱心網友回復：
這將更容易，更清潔，最終更快，而無需使用任何游標 - 就像這樣：
ALTER PROCEDURE [dbo].[Myproc]
AS
INSERT INTO [dbo].[tableA] ([Name], [Age])
SELECT Name, Age
FROM tableName;
DECLARE @RowsInsertedStmt1 INT;
SELECT @RowsInsertedStmt1 = @@ROWCOUNT;
INSERT INTO [dbo].[tableA] ([Name], [Age])
SELECT Name, Age
FROM tableName;
DECLARE @RowsInsertedStmt2 INT;
SELECT @RowsInsertedStmt2 = @@ROWCOUNT;
-- returned the combined count to the caller of the stored procedure
SELECT TotalCount = @RowsInsertedStmt1 @RowsInsertedStmt2;
使用@@ROWCOUNT，您可以找出插入了多少行。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/489853.html