Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-03-27 : 23:16:45
|
Yuriy writes "This is on SQL 7 running on Windows 2000 PRO.
I'm trying to write a stored procedure based on the "Returning Rows in Random Order" article by sqlguru.
It is to select a given number of random records from a table. I'm using the database Northwind and table Suppliers. here's the procedure:
CREATE PROCEDURE sp_random AS
CREATE TABLE #temp (ID int NOT NULL, RandNum float NULL)
INSERT #temp (ID)
SELECT supplierID FROM Suppliers
DECLARE Randomizer CURSOR
FOR SELECT * FROM #temp
OPEN Randomizer
FETCH NEXT FROM Randomizer
WHILE @@Fetch_Status != -1
BEGIN
UPDATE #temp SET RandNum = rand()
WHERE CURRENT OF Randomizer
FETCH NEXT FROM Randomizer
END
CLOSE Randomizer
DEALLOCATE Randomizer
SELECT * from #temp LEFT JOIN Suppliers
ON #temp.ID = Suppliers.SupplierID order by RandNum
All i get returned to me is one row.
ID RandNum
19 NULL
what's the deal ? ?
And how can I limit the number of rows my query selects ? is there a LIMIT statement in MSSQL ?
Please help me out.
TIA" |
|
|
|
|
|
A stored procedure selecting a number of random records.