slight modification of SP - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

slight modification of SP

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-07-08 : 14:12:49

I have a slight problem with the SP below. Everything works but the problem is that the SELECT statment that gets inserted into the TEMP table sometimes returns too few records. How can I make it so that it returns minimum 500 records get inserted into the temp table when the SELECT statement returns less than that. I can get rid of the "AND votes < 32" from the SELECT statment to get more records, however if I do this I want to SELECT all the "AND votes < 32" records plus any other records, preferably ones with lower votes.

I hope this makes enough sense, if I need to explain better please let me know ;)

Mike

CREATE PROCEDURE select_RandomRecord
(
@genderID tinyint
)
AS
-- Create a temporary table with the same structure of userIDm nameOnline
-- the table we want to select a random record from
CREATE TABLE #TempRandomUser
(
idNum int identity(1,1),
nameOnline varchar(15),
userID int
)
declare @nRandNum int, @nRecordCount int
begin
-- Dump the contents of the table to seach into the
-- temp. table
IF @genderID > 2
INSERT INTO #TempRandomUser(nameonline, userid) SELECT nameOnline, userID FROM tblUserDetails WHERE active = 1 AND votes < 32
ELSE
INSERT INTO #TempRandomUser(nameonline, userid) SELECT nameOnline, userID FROM tblUserDetails WHERE genderID = @genderID AND active = 1 AND votes < 32
-- Get the number of records in our temp table
Select @nRecordCount = count(userID) From #TempRandomUser
-- Select a random number between 1 and the number
-- of records in our table
Select @nRandNum = Round(((@nRecordCount) * Rand() + 1), 0)
-- Select the record from the temp table with the
-- ID equal to the random number selected...
Select nameOnline, userID From #TempRandomUser
Where idNum = @nRandNum
end

GO

Edited by - mike123 on 07/08/2002 14:13:47

gwhiz
Yak Posting Veteran

78 Posts

Posted - 2002-07-08 : 16:51:44

I would think you would have to check a record count for the select and then use something like this to return 500 rows.

INSERT INTO #TempRandomUser(nameonline, userid) SELECT TOP 500 nameOnline, userID FROM tblUserDetails WHERE active = 1
ORDER BY votes

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-07-09 : 22:57:57

thanks, I still cant figure out the proper way to do this .. anybody?

cheers

mike

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources