Running a Contest with SQL Server
1 Comment
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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:
|
1 2 |
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:
|
1 2 3 4 5 6 7 8 |
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.
