mardi 4 août 2015

Stored Procedure in cursor firing only once

I am getting an output from a stored procedure but in my cursor it only returns the first value.

SP 1

ALTER PROCEDURE [dbo].[register_system_email_audits]
    @UserId int,
    @EmailFor varchar(500),
    @DateSent datetime,
    @UniqueKey varchar(20) output
AS
BEGIN
    INSERT INTO [SystemEmailsAudit]
           ([UserId]
           ,[EmailFor]
           ,[DateSent]
           ,[UniqueKey]
           )
     VALUES
           (@UserId 
           ,@EmailFor
           ,@DateSent
           ,(SELECT CAST( CAST(RAND() * 100000000 AS int) as varchar(20)))
           );
     SELECT @UniqueKey=s.UniqueKey FROM [SystemEmailsAudit] s 
        WHERE s.RecordId=SCOPE_IDENTITY();

END

SP2

ALTER PROCEDURE [SendNewsletterMails]
(
    @nLID int,
    @Category VARCHAR(50)
)
as
DECLARE
  @html varchar(max),
  @Description VARCHAR(100),
  @Subject varchar(50),
  @Email varchar(100),
  @listID   int,
  @DLC smalldatetime,
  @Date DATETIME = NULL
    set @html = (SELECT html from NewsLetter where nLID=@nLID)
    DECLARE crsEmailList CURSOR FOR
    SELECT email, ListID from lists where category=@Category AND (DLC < DATEADD(DAY, -1,GETDATE()) OR DLC IS NULL)
  OPEN crsEmailList
  FETCH NEXT FROM crsEmailList INTO @email, @ListID
    while @@FETCH_STATUS = 0 BEGIN
    --Add Beacon
    DECLARE @UniqueKey varchar(20)
    EXEC [register_system_email_audits] @ListID, @email, @Date, @UniqueKey output
    SET @html = Replace(@html,'[keyvalue]', @UniqueKey)
    EXEC msdb.dbo.sp_send_dbmail 
      @profile_Name ='Local Server',
       @recipients= @email ,
       @subject = @Subject,
       @body = @html,
       @body_format='HTML'
    FETCH NEXT FROM crsEmailList INTO @email, @ListID
    END
  CLOSE crsEmailList
  DEALLOCATE crsEmailList
GO

The stored procedure returns the proper @UniqueKey but only for the first record in the cursor. I have been contemplating a while loop or a temp table but settled on the cursor route for now.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire