SET NOCOUNT ON and Cursors

Question

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;

Answer 1

SET NOCOUNT ON is 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?

Answer 2

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.

Answer 3

You need to direct the FETCH into variable inside the loop as well:

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