USE tempdb;
GO
--Safety Check
IF OBJECT_ID('dbo.AddressTable','U') IS NOT NULL
BEGIN
DROP TABLE dbo.AddressTable;
END
GO
--Safety Check
IF OBJECT_ID('dbo.LogTable','U') IS NOT NULL
BEGIN
DROP TABLE dbo.LogTable;
END
GO
--Safety Check
IF OBJECT_ID('dbo.proc_SendEmails','P') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.proc_SendEmails;
END
GO
--Create required objects
CREATE TABLE dbo.AddressTable
([PersonId] INT NOT NULL,
[EmailAddress] NVARCHAR(255) NOT NULL,
[Status] BIT NOT NULL
);
GO
CREATE TABLE dbo.LogTable
([PersonId] INT NOT NULL,
[ProcessDate] DATETIME NOT NULL
);
GO
--Assume this SP is sending the E-mails
CREATE PROCEDURE dbo.proc_SendEmails
@batchSize INT
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #someTable ([DummyValue] INT NULL,
[DummyDate] DATETIME NULL
)
--When you fetch the E-mails to process,
--log the Ids into your log table
INSERT INTO #someTable (DummyValue, DummyDate)
OUTPUT inserted.DummyValue,
inserted.DummyDate
INTO dbo.LogTable ([PersonId], [ProcessDate])
SELECT TOP (@batchSize)
at.PersonId AS DummyValue,
GETDATE() AS DummyDate
FROM dbo.AddressTable AS at
LEFT OUTER JOIN dbo.LogTable AS lt ON at.PersonId = lt.PersonId
WHERE at.[Status] = 1
AND at.PersonId IS NOT NULL
AND lt.PersonId IS NULL;
END
GO
--Generate some test data. NOTE: I have taken test data from the AdventureWorks sample database
USE tempdb;
GO
INSERT INTO dbo.AddressTable (PersonId, EmailAddress, [Status])
SELECT ea.BusinessEntityID,
ea.EmailAddress,
CASE (ea.BusinessEntityID % 2) WHEN 1 THEN 1 ELSE 0 END AS [Status]
FROM AdventureWorks2012.Person.EmailAddress AS ea;
GO
--Now, keep running the SP and observe the values logged in dbo.LogTable
USE tempdb;
GO
EXEC dbo.proc_SendEmails @batchSize = 5;
GO