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