Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Raffle SQL Server Style

By Alex Tocitu,

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.

Total article views: 3307 | Views in the last 30 days: 2
 
Related Articles
FORUM

if Exists

if EXISTS(SELECT Sr_No FROM #Temp_Table_Col WHERE Sr_No = @iIndexCol)

FORUM

SQL Tuning where no PK exists

Trying to tune a query on a table where no PK exists

FORUM

“SELECT 1″ rather than a “SELECT *” when using an EXISTS or a NOT EXISTS clause”.

“SELECT 1″ rather than a “SELECT *” when using an EXISTS or a NOT EXISTS clause”.

FORUM

Select premission was denied on Object

Select premission Denied on Object

BLOG

fn_OBJECT_TYPE

I recently started using OBJECT_NAME, OBJECT_SCHEMA_NAME and OBJECT_ID functions; unfortunately I re...

Tags
raffle    
random    
t-sql    
user group    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones