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)
Posts
6 comments:
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
Thats great Denis, I was thinking about CLR Stored procedures :)
-SS
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()
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
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)
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