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.
