There is one traditional and exciting thing at the end of every SQL Server meeting at our Atlanta based user group: the raffle! This is when the swag is handed out to the lucky winners. The method we have employed in the past was to get a roll of raffle tickets, the kind one can find at office supplies stores, and hand half of a ticket to meeting participants. We put the other half in a bag or box, shuffle the tickets in the box, and then started drawing them out. Usually the numbers on the tickets were long and hard to read, which added to the time the process took to complete.
The person(s) handling the raffles asked for the community to help find a better solution. This is when we designed this automated approach, the SQL-Server-way. The article presents the method employed, and also the scripts being used.
We begin (of course) by creating a database, called [RaffleDb]See scripts below:
------------------------------------------------------------ USE [master] GO -- IF EXISTS (SELECT name FROM sys.databases WHERE name = N'RaffleDb') DROP DATABASE [RaffleDb] GO -- IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'RaffleDb') BEGIN CREATE DATABASE [RaffleDb] END GO ------------------------------------------------------------
We continue by creating a table, called [Raffle], which will hold a random value string. The field [RaffleId] is an identity column and will hold a number from 1 to the total number of participants in the raffle. The field [Selected] is of a bit type and will be updated to 1 whenever the [RaffleId] is picked.
The stored procedure [RAFFLE_SETUP] (see below) contains the code to create the table above and to fill it up with a number of records equal to the number of raffle participants.
------------------------------------------------------------ USE [RaffleDb] GO -- IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RAFFLE_SETUP]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[RAFFLE_SETUP] GO -- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RAFFLE_SETUP]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[RAFFLE_SETUP](@Count int) AS BEGIN -- IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[DF_Raffle_Selected]'') AND type = ''D'') BEGIN ALTER TABLE [dbo].[Raffle] DROP CONSTRAINT [DF_Raffle_Selected] END -- IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[DF_Raffle_RandomValue]'') AND type = ''D'') BEGIN ALTER TABLE [dbo].[Raffle] DROP CONSTRAINT [DF_Raffle_RandomValue] END -- IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[Raffle]'') AND type in (N''U'')) DROP TABLE [dbo].[Raffle] -- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[Raffle]'') AND type in (N''U'')) BEGIN CREATE TABLE [dbo].[Raffle]( [RaffleId] [int] IDENTITY(1,1) NOT NULL, [RandomValue] [char](36) NOT NULL, [Selected] [bit] NOT NULL, CONSTRAINT [PK_Raffle] PRIMARY KEY CLUSTERED ( [RaffleId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] END -- IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[DF_Raffle_RandomValue]'') AND type = ''D'') BEGIN ALTER TABLE [dbo].[Raffle] ADD CONSTRAINT [DF_Raffle_RandomValue] DEFAULT (newid()) FOR [RandomValue] END -- IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[DF_Raffle_Selected]'') AND type = ''D'') BEGIN ALTER TABLE [dbo].[Raffle] ADD CONSTRAINT [DF_Raffle_Selected] DEFAULT ((0)) FOR [Selected] END -- DECLARE @Counter int = 0 SET NOCOUNT ON WHILE @Counter < @Count BEGIN INSERT Raffle VALUES(DEFAULT, DEFAULT) SET @Counter = @Counter + 1 END SET NOCOUNT OFF -- END
' END GO ------------------------------------------------------------
To draw a number, use stored procedure [RAFFLE_DRAW] shown below:
------------------------------------------------------------ USE [RaffleDb] GO -- IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RAFFLE_DRAW]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[RAFFLE_DRAW] GO -- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RAFFLE_DRAW]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[RAFFLE_DRAW] AS BEGIN DECLARE @RaffleId int SET NOCOUNT ON SELECT TOP 1 @RaffleId = RaffleId FROM Raffle WHERE Selected = 0 ORDER BY RandomValue IF @RaffleId IS NOT NULL BEGIN UPDATE Raffle SET Selected = 1 WHERE RaffleId = @RaffleId SELECT @RaffleId AS [Ticket #] END ELSE BEGIN PRINT ''All raffle tickets have been picked!'' END SET NOCOUNT OFF END' END GO ------------------------------------------------------------
Running [RAFFLE_DRAW] for each prize will pick the winning number and mark it as selected. This disallows that particular number to be picked again.
At any time during the raffle the stored procedure [RAFFLE_REVIEW] may be used to list all the drawn numbers in the order they were selected.
------------------------------------------------------------ USE [RaffleDb] GO -- IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RAFFLE_REVIEW]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[RAFFLE_REVIEW] GO -- IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RAFFLE_REVIEW]') AND type in (N'P', N'PC')) BEGIN EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[RAFFLE_REVIEW] AS BEGIN SELECT RaffleId AS [Ticket #] FROM Raffle WHERE Selected = 1 ORDER BY RandomValue END' END GO ------------------------------------------------------------
Scripts below show a sample run:
------------------------------------------------------------ -- SHUFFLE UP !!! ------------------------------------------------------------ -- USE [RaffleDb] GO -- RAFFLE_SETUP 37 GO -- --run a number of times equal to the number of raffle prizes RAFFLE_DRAW GO -- --run any time there is a need for review RAFFLE_REVIEW GO ------------------------------------------------------------
Additional scripts (shown below) may be used for cleanup.
------------------------------------------------------------ -- RAFFLE_CLEANUP ------------------------------------------------------------ -- USE [RaffleDb] GO -- IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RAFFLE_SETUP]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[RAFFLE_SETUP] GO ------------------------------------------------------------ USE [RaffleDb] GO -- IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RAFFLE_DRAW]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[RAFFLE_DRAW] GO ------------------------------------------------------------ USE [RaffleDb] GO -- IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RAFFLE_REVIEW]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[RAFFLE_REVIEW] GO ------------------------------------------------------------ -- USE [RaffleDb] GO -- IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Raffle]') AND type in (N'U')) DROP TABLE [dbo].[Raffle] GO -- ------------------------------------------------------------ -- USE [master] GO -- IF EXISTS (SELECT name FROM sys.databases WHERE name = N'RaffleDb') DROP DATABASE [RaffleDb] GO -- ------------------------------------------------------------ -- END ------------------------------------------------------------
This concludes our adventure in the magic world of raffles, showing a possible implementation for the amusement of SQL Server fans.
