This week another "style" of coding kept my attention. Problem that people were trying to solve was a little bit more complex, but I'll build a simple example to illustrate the idea that I'm talking about today. We will define tables with minimal fields for 2 entities: Customers and Orders.
CREATE TABLE Orders
(
OrderId INT IDENTITY(1,1) PRIMARY KEY,
CustomerId INT,
OrderNumber VARCHAR(15),
OrderDate DATETIME,
OrderAmount NUMERIC(10,4)
)
CREATE TABLE Customers
(
CustomerId INT IDENTITY(1,1) PRIMARY KEY,
CustomerName VARCHAR(50),
CustomerAddress VARCHAR(255),
EmailAddress VARCHAR(255),
Active TINYINT
)
Business requirement was to insert into table MailShot email address of every customer that place an order with a delay no longer than 10 minutes. Mail shot table is used by another application that is responsible for sending welcome letters; definition of the table below.
CREATE TABLE MailShot
(
MailShotId INT IDENTITY(1,1) PRIMARY KEY,
EmailAddress VARCHAR(255)
)
That problem was solved in a very special way. A stored procedure (see below) was created and scheduled to run into a job step every 5 minute.
CREATE PROCEDURE PopulateMailShot
(@LastOrderIdProcessed INT)
AS
BEGIN
DECLARE @counter INT
DECLARE @maxId INT
DECLARE @cntOrders INT
DECLARE @CustomerId INT
DECLARE @EmailAddress VARCHAR(255)
DECLARE @cl TABLE
(id INT IDENTITY(1,1),
CustomerId INT,
EmailAddress VARCHAR(255)
)
INSERT INTO @cl
SELECT CustomerId, EmailAddress
FROM Customers
WHERE Active = 1
SELECT
@counter = MIN(id),
@maxId = MAX(id) + 1
FROM @cl
WHILE @counter < @maxId
BEGIN
SELECT
@CustomerId = id, @EmailAddress=EmailAddress
FROM @cl
WHERE id = @counter
SET @cntOrders=0
SELECT @cntOrders=COUNT(*)
FROM Orders
WHERE CustomerId = @CustomerId
AND OrderId > @LastOrderIdProcessed
IF @cntOrders > 0
BEGIN
INSERT INTO MailShot (EmailAddress) VALUES (@EmailAddress)
END
SET @counter = @counter + 1
END
END
Great isn't it? The most elegant way to solve such a problem is to use an insert trigger on Orders table. But there is also another way if the use of triggers in prohibited. Isn't it much more simple and efficient to solve everything from a single SQL statement instead of that crazy row-by-row processing?
Procedure body, doing exactly the same thing like this:
INSERT INTO MailShot
(EmailAddress)
SELECT DISTINCT
C.EmailAddress
FROM
Orders O INNER JOIN Customers C
ON O.CustomerId = C.CustomerId
WHERE O.OrderId > @LastOrderIdProcessed