My employer sells packages to events, such as U2’s current
Vertigo concert tour. Since numerous clients and prospects visit our Web site to buy a package, we deemed it necessary to give them a 10-minute window
in which to complete the transaction. We devised a way to place a “hold”
on the number of tickets requested and then release the tickets back into the
available inventory if the 10-minute window expires. This is a great solution
if your e-commerce site offers a limited number of items that often sell out
How I built the solution
First, I created a table called OnLineOrders,
which contains two columns of interest:
- DateTimeEntered: a DateTime
column whose default is GetDate().
- HoldExpires: a calculated column whose formula is DateAdd(mi,
For simplicity’s sake, on subsequent queries regarding
available inventory, I chose to add another column called IsHold
In this application, deciding how much inventory is
available involves several business rules of little general interest. There is
a view (and a similar stored procedure) which determines the actual number by
looking in several “bins” and then subtracting the tickets currently
on hold for the Web site users.
If the Web user doesn’t complete a transaction in 10 minutes,
the hold is released. To achieve this, I created a simple stored procedure
called HoldsRelease_ap, which looks like this:
CREATE PROCEDURE [dbo].[ReleaseHolds_ap]
SET IsHold = 0
WHERE HoldExpires < CURRENT_TIMESTAMP
AND IsHold = 1
Follow these steps to schedule this procedure to run every
Enterprise Manager, select the database and its
Stored Procedures node.
in the Stored Procedures window and select New Stored Procedure.
the stored procedure, check its syntax, and save it.
the Enterprise Manager menu, select Tools | Job Scheduling. This invokes a
wizard whose first page contains an Option button set from which you’ll
want to select the Transact SQL Command. Click Next.
the next page, in the big box, type EXEC
HoldsRelease_ap and click Next.
invokes a form that enables you to control the job. The current selection
is Run Now, but instead you’ll want to select Recurring Job. This enables
the Schedule button, which you should press.
the options that are appropriate to your needs. In my case, the options
were Run Daily, Every 10 Minutes.
next page allows you to augment your job by sending e-mail or net send
messages. In my case neither was relevant, but your needs may differ.
SQL Server will now run the job every 10 minutes, changing
the IsHold flag on the appropriate rows in the OnLineOrders table. The inventory calculations will then
ignore the rows affected.
management decides to change the window length from say 10 minutes to 20
minutes, here’s how you would adjust the job schedule in Enterprise Manager:
the Management node.
the SQL Server Agent node.
on the job of interest.
TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!