Blog

If you ever need to run a random drawing contest with a SQL Server database back end, here’s one way to do it:

Create a table to hold each person’s name and email address.  We use EntryID as a primary key, not their name or email, because a person can enter more than once.

CREATE TABLE [dbo].[Entry](
    [EntryID] [int] IDENTITY(1,1) NOT NULL,
    [FullName] [varchar](50) NOT NULL,
    [EmailAddress] [varchar](100) NOT NULL,
    [PrizeWon] [varchar](50) NULL,
 CONSTRAINT [PK_Entry] PRIMARY KEY CLUSTERED
(
    [EntryID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
  ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Insert a record each time somebody enters:

INSERT INTO [dbo].[Entry] ([FullName], [EmailAddress])
  VALUES ('Hugo Reyes', 'big-money@no-whammies.com')

Draw a random winner by running this script for each prize:

DECLARE @WinningEntryID INT
SET @WinningEntryID = (SELECT TOP 1 [EntryID] 
  FROM [dbo].[Entry] 
  WHERE [PrizeWon] IS NULL 
  ORDER BY NEWID())
UPDATE [dbo].[Entry]
  SET [PrizeWon] = 'Pound of Bacon'
  WHERE [EntryID] = @WinningEntryID

The “order by newid()” part will give us a random record out of the table.  The “where PrizeWon is null” part will make sure we don’t draw an entry again after it’s already won.