Oct 5, 2006

A scenario to ponder #1

For all those using SQL Server 2005, here is a scenario, see if you can come up with a solution:
I thought I will check out the different implementations possible and find the best out of it.

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)

6 comments:

Denis the SQL Menace said...

What about this?

create proc ReturnRandomCustomers
as
set nocount on
declare @value int

select @value = cast(5 + (rand() * (20 - 5 + 1)) as integer)

--change to set rowcount @value in sql 2000
select top @value *
from Customers
Order by NEWID()
set nocount off
GO

Denis

Anonymous said...

Thats great Denis, I was thinking about CLR Stored procedures :)
-SS

Omnibuzz said...

Exactly what I was expecting Denis... Well, Truth is didn't expect a response that fast anyways.. hehe..

Will make it a bit tougher next time

Here is what I had in mind..Same as yours, just for the records..

Solution:


select
top (cast(5 + rand()*15 as int)) * from customers
order by newid()

Denis the SQL Menace said...

I created a challenge of my own, it also has to do with returning random results but with a twist

Here is the link, good luck

http://sqlservercode.blogspot.com/2006/10/sql-challenge-random-grouping-of-data.html

Anonymous said...

Here's one that works under SQL2000. Same concept of course, just uses a workaround to the lack of variables in the TOP clause.

Of course, you could do some funky stuff where you work out a row_number equivalent for each one, and then use that to filter it some more.

declare @rndcnt int
set @rndcnt = abs(checksum(newid())) % 15 + 5
declare @qry varchar(50)
set @qry = 'select top ' + cast(@rndcnt as char(2)) + ' * from Customers order by newid()'
exec (@qry)

Omnibuzz said...

Rob,
Just to share my thoughts, I would rather go with the solution Denis gave for SQL Server 2000 than with dynamic SQL... Nothing wrong with the approach... I am not very comfortable with dynamic SQL and would like to avoid it where ever possible :) Just the same old arguements, caching, security, authorization and all..

But I should accept that its a perfectly viable approach
-Omni

Post a Comment