SET NOCOUNT ON 和游标
我有一个存储过程，它调用多个存储过程，每个存储过程都将虚拟数据插入到一个表中。它工作正常，除了对于游标中的每个循环显示一行结果 - 仅显示 ClubcardId = 2、ClubcardId = 3 等。
我已经使用了 SET NOCOUNT ON 但这似乎没有帮助。我正在寻找这个存储过程来创建几百万行，因此，SQL 打印每行的结果将是一个问题。
任何人都可以建议如何防止显示输出。我已复制下面的父存储过程。我可以确定显示不是来自子存储过程 - lap_CreateClubcardTransaction。
如果我将：
DECLARE Clubcard_Cursor CURSOR FAST_FORWARD FOR
SELECT ClubcardId FROM Clubcard
...更改为：
DECLARE Clubcard_Cursor CURSOR FAST_FORWARD FOR
SELECT ClubcardId as 'TEST' FROM Clubcard
...那么我会得到为光标的每一行显示的值“TEST”。
这是父存储过程：
ALTER PROCEDURE [dbo].[lap_CreateDummyData]
AS
SET NOCOUNT ON
DECLARE @NumberOfCustomers bigint
DECLARE @NumberOfTransactions bigint
SET @NumberOfCustomers = 50000
SET @NumberOfTransactions = 10
EXEC lap_CreateCustomer @NumberOfCustomers = @NumberOfCustomers;
EXEC lap_CreateCustomerPreference @NumberOfCustomers = @NumberOfCustomers;
EXEC lap_CreateClubCard @NumberOfCustomers = @NumberOfCustomers;
EXEC lap_CreateClubCardOffer @NumberOfCustomers = @NumberOfCustomers;
--get static data details to use when creating transaction records
DECLARE @TransactionType tinyint
DECLARE @TransactionReasonID tinyint
DECLARE @TescoStoreID int
DECLARE @PartnerID bigint
DECLARE @PartnerOutletID bigint
DECLARE @ClubcardID bigint
SET @TransactionType = (SELECT TOP 1 TransactionType FROM TransactionType)
SET @TransactionReasonID = (SELECT TOP 1 TransactionReasonID FROM TransactionReason)
SET @TescoStoreID = (SELECT TOP 1 TescoStoreId FROM TescoStore)
SET @PartnerID = (SELECT TOP 1 PartnerID FROM PartnerOutlet)
SET @PartnerOutletID = (SELECT TOP 1 PartnerOutletID FROM PartnerOutlet)
DECLARE Clubcard_Cursor CURSOR FAST_FORWARD FOR
SELECT ClubcardId FROM Clubcard
OPEN Clubcard_Cursor
FETCH NEXT FROM Clubcard_Cursor
INTO @ClubcardID SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC lap_CreateClubcardTransaction @NumberOfTransactions = @NumberOfTransactions, @ClubcardID = @ClubcardID, @TransactionType = @TransactionType, @TransactionReasonID = @TransactionReasonID, @TescoStoreId = @TescoStoreID, @PartnerID = @PartnerID, @PartnerOutletID = @PartnerOutletID;
FETCH NEXT FROM Clubcard_Cursor;
END;
CLOSE Clubcard_Cursor;
DEALLOCATE Clubcard_Cursor;
I have a stored proc that calls several store procs, each of which insert dummy data into a single table each. It works fine except that for each loop in the cursor a single row of results is dispayed - just showing ClubcardId = 2, ClubcardId = 3 etc.
I have used the SET NOCOUNT ON but this doesn't seem to help. I'm looking for this stored proc to create several million rows so, SQL printing the result for each row will be an issue.
Could anyone please advise how to prevent the output from being displayed. I have copied the parent stored proc below. I can be sure that the display is not coming from the child stored proc - lap_CreateClubcardTransaction.
If I change:
DECLARE Clubcard_Cursor CURSOR FAST_FORWARD FOR
SELECT ClubcardId FROM Clubcard
...to:
DECLARE Clubcard_Cursor CURSOR FAST_FORWARD FOR
SELECT ClubcardId as 'TEST' FROM Clubcard
...then the I get the value 'TEST' displayed for each row of the cursor.
Here's the parent stored proc:
ALTER PROCEDURE [dbo].[lap_CreateDummyData]
AS
SET NOCOUNT ON
DECLARE @NumberOfCustomers bigint
DECLARE @NumberOfTransactions bigint
SET @NumberOfCustomers = 50000
SET @NumberOfTransactions = 10
EXEC lap_CreateCustomer @NumberOfCustomers = @NumberOfCustomers;
EXEC lap_CreateCustomerPreference @NumberOfCustomers = @NumberOfCustomers;
EXEC lap_CreateClubCard @NumberOfCustomers = @NumberOfCustomers;
EXEC lap_CreateClubCardOffer @NumberOfCustomers = @NumberOfCustomers;
--get static data details to use when creating transaction records
DECLARE @TransactionType tinyint
DECLARE @TransactionReasonID tinyint
DECLARE @TescoStoreID int
DECLARE @PartnerID bigint
DECLARE @PartnerOutletID bigint
DECLARE @ClubcardID bigint
SET @TransactionType = (SELECT TOP 1 TransactionType FROM TransactionType)
SET @TransactionReasonID = (SELECT TOP 1 TransactionReasonID FROM TransactionReason)
SET @TescoStoreID = (SELECT TOP 1 TescoStoreId FROM TescoStore)
SET @PartnerID = (SELECT TOP 1 PartnerID FROM PartnerOutlet)
SET @PartnerOutletID = (SELECT TOP 1 PartnerOutletID FROM PartnerOutlet)
DECLARE Clubcard_Cursor CURSOR FAST_FORWARD FOR
SELECT ClubcardId FROM Clubcard
OPEN Clubcard_Cursor
FETCH NEXT FROM Clubcard_Cursor
INTO @ClubcardID SET NOCOUNT ON
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC lap_CreateClubcardTransaction @NumberOfTransactions = @NumberOfTransactions, @ClubcardID = @ClubcardID, @TransactionType = @TransactionType, @TransactionReasonID = @TransactionReasonID, @TescoStoreId = @TescoStoreID, @PartnerID = @PartnerID, @PartnerOutletID = @PartnerOutletID;
FETCH NEXT FROM Clubcard_Cursor;
END;
CLOSE Clubcard_Cursor;
DEALLOCATE Clubcard_Cursor;
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您还需要将 FETCH 定向到循环内的变量：
You need to direct the FETCH into variable inside the loop as well:
在任何情况下，我都不会使用游标一次插入一百万行。这需要几个小时。这是一个游标使用不当的例子。创建一个将执行基于集合的操作的过程。
Under no circumstances would I use a cursor to insert a million rows one row at a time. That will take hours. This is an example of a poor use of a cursor. Create a proc that will do a set-based operation.
SET NOCOUNT ON在获取中没有用，因此从那里删除它。 lap_CreateClubcardTransaction 的代码中似乎包含一个 SELECT 语句。你能检查一下这是否属实吗？SET NOCOUNT ONis useless inside the fetch, so remove it from there. It seems lap_CreateClubcardTransaction contains a SELECT statement inside its code. Can you check if this is true?