Thursday, October 05, 2006

Answer To A SQL Challenge By Omnibuzz (SQL Garbage Collector)

Omnibuzz has posted the following challenge: A scenario to ponder #1
This challenge is about returning a random number of customers and returnig them in random order, here is what he said:

Say you have a table:

Customers (CustomerID int primary key, CustomerName varchar(50))

A pretty simple table structure. And it has 1000 rows.
Now, I am conducting a contest for the customers where I will randomly pick up 5 to 20 customers every week and give away prizes.

How will I go about doing it?

I need to create a stored procedure/query/function that will accept no parameters but will return random list of customers and random number of customers (between 5 and 20)


And here is my solution

CREATE PROCEDURE ReturnRandomCustomers
AS
SET NOCOUNT ON
DECLARE
@value INT

SELECT
@value = CAST(5 + (RAND() * (20 - 5 + 1)) AS INT)


SELECT TOP @value *
FROM Customers
ORDER BY NEWID()
SET NOCOUNT OFF
GO

Since we are using SQL Server 2005 we can use TOP with a variable, and to set that variable we us the RAND function

The SQL Server 2000 version would look like this

CREATE PROCEDURE ReturnRandomCustomers
AS
SET NOCOUNT ON
DECLARE
@value INT

SELECT
@value = CAST(5 + (RAND() * (20 - 5 + 1)) AS INT)

SET ROWCOUNT @value
SELECT *
FROM Customers
ORDER BY NEWID()

SET ROWCOUNT 0
SET NOCOUNT OFF
GO

No comments: