SET NOCOUNT ON and Cursors

Question

我有一个存储过程，它调用几个存储过程，每个存储过程都将虚拟数据插入到一个表中。它工作得很好，除了游标中的每个循环都显示一行结果--只显示ClubcardId = 2，ClubcardId =3等。

我已经在上使用了SET NOCOUNT，但这似乎没有帮助。我正在寻找这个存储过程来创建数百万行，因此，SQL打印每一行的结果将是一个问题。

谁能告诉我如何防止输出被显示。我已经复制了下面的父存储进程。我可以确定显示不是来自子存储的proc - lap_CreateClubcardTransaction。

如果我改变了：

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

测试I获取为游标的每行显示的值‘...then’。

下面是父存储的进程：

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;

Answer 1

您还需要将FETCH into变量定向到循环中：

WHILE ...
BEGIN
  ...
  FETCH NEXT FROM Clubcard_Cursor INTO @ClubcardID
END

Answer 2

在任何情况下，我都不会使用游标一次一行地插入一百万行。这需要几个小时。这是一个游标使用不当的例子。创建一个将执行基于集合的操作的proc。

Answer 3

SET NOCOUNT ON在fetch中是无用的，所以从那里删除它。lap_CreateClubcardTransaction似乎在其代码中包含了一条SELECT语句。你能确认一下这是不是真的吗？