Find Code:
All Words
Any of the Words
Exact Phrase
Home
:
Code
:
Forums
:
Submit
:
Mailing List
:
About
:
Contact
Code
All
VB.NET
ASP.NET
C#
VB Classic
ASP Classic
Snippets
Popular
Resources
Submit Code
Forums
Articles
Tips
Links
Books
Contest
Link to us
SQL Server 2005 Table Shuffle
Author:
Destroyer
Submitted:
2/26/2006
Version:
VB.NET 2005
Compatibility:
VB.NET 2003, VB 2005
Category:
Databases
Views:
15010
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'
Home
|
Forums
|
Submit
|
Books
|
Mailing List
|
Advertising
|
About
|
Contact
© 2024 A1VBCode. All rights reserved.
Legal disclaimer & terms of use
Privacy statement