You should know that the random function in SQL Server really, really sucks. Unless you can seed it with a number that changes constantly every ti me before you call the function, you'll get lots of unrandom numbers.
-----Original Message----- From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]] Sent: February 13, 2002 4:06 PM To: CF-Talk Subject: RE: Random row from SQL Ok .. your code worked (almost). In T-SQL, there is no INT function. However, there IS a "CEILING" function. The only thing I had to work around was that the number "1" would never show up. I managed to get it the re by adding 1 to the equation within the Ceiling function and subtracting it outside of the ceiling function. The code is below: Mark ***************************** CREATE PROCEDURE Sp_GetAdTotal_new @venueCode int = 3, @Rowseed int OUTPUT WITH RECOMPILE AS SET NOCOUNT OFF DECLARE @chosenRow int DECLARE @AdPk int DECLARE Ads CURSOR KEYSET FOR SELECT Adnumber FROM AdBanners WHERE Active = 1 AND VenueCode = @Venuecode OPEN Ads SELECT @Rowseed = @@Cursor_rows IF @Rowseed > 0 BEGIN SELECT @chosenRow = CEILING(RAND() * (@@Cursor_rows + 1)) - 1 FETCH ABSOLUTE @chosenRow FROM Ads INTO @AdPk CLOSE Ads DEALLOCATE Ads .... do some tracking queries.... SELECT AdNumber,filename,alttext,url,btext,hdtext,infotext,ad_url,isurl,trac ker,adt ext FROM AdBanners WHERE AdNumber = @AdPk END ELSE CLOSE Ads DEALLOCATE Ads _____________________________________________________________________ _ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ______________________________________________________________________ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
