SQL Server 2005 Table Shuffle - Visual Basic , VB.NET

	Find Code:
Home : Code : Forums : Submit : Mailing List : About : Contact

All

VB.NET

ASP.NET

C#

Snippets

Popular

Forums

Articles

Tips

Links

Books

Contest

SQL Server 2005 Table Shuffle
Author: Destroyer
Submitted: 2/26/2006
Version: VB.NET 2005
Compatibility: VB.NET 2003, VB 2005
Category: Databases
Views: 15011
SQL Server stored procedure to randomly shuffle records retured by a Select query.
Declarations:
'none
Code:
CREATE PROCEDURE Shuffle @Title varchar(20) AS SET nocount on declare @rand_holder float CREATE TABLE #temp (ID int NOT NULL, Name VarChar(20), Title VarChar(50), Address VarChar(255), RandNum float NULL) INSERT #temp (id,name,title,address) SELECT id,name,title,address FROM tblemployees WHERE Title = @Title --now, assign a new random value to each key value in #temp DECLARE Randomizer CURSOR FOR SELECT RandNum FROM #temp OPEN Randomizer FETCH NEXT FROM Randomizer into @rand_holder WHILE @@Fetch_Status != -1 BEGIN UPDATE #temp SET RandNum = rand() WHERE CURRENT OF Randomizer FETCH NEXT FROM Randomizer into @rand_holder END CLOSE Randomizer DEALLOCATE Randomizer SELECT * FROM #temp ORDER BY RandNum GO 'call using EXECUTE Shuffle @Title = 'manager'