Well, you could use the RAND() function to get a random number, multiply it by GETDATE(), divide by 100000 and use INT() to return a whole number... but that would be a lot of SQL.
You are better off creating the variable in CF and passing it into the SP. Random numbers in SQL Server aren't really as random as we would like; once you reset the server the random number counter resets and you get a repeating pattern. Mike -----Original Message----- From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 13, 2002 2:44 PM To: CF-Talk Subject: RE: Random row from SQL Yes, I know how to do this in CF - I was wondering if anyone had figured out a way to do it within a Stored proc? something like.... OPEN CURSOR MyAds FETCH NEXT ABSOLUTE Randomize(1, @maxCursorrows) INTO @ChosenRow ............... I've tried using the "RAND" Function, but it is does not produce a randome number within a given range - it only does 0 to 1 using a seed value. Mark -----Original Message----- From: C. Hatton Humphrey [mailto:[EMAIL PROTECTED]] Sent: Wednesday, February 13, 2002 9:37 AM To: CF-Talk Subject: RE: Random row from SQL >From memory, so the syntax might be a touch off... <cfset temp=Randomize(Seconds(Now()) * Minutes(Now()))> <cfset ShowRow = RandRange(1, qGetAds.Recordcount)> <cfoutput query="qGetAds" StartRow=ShowRow MaxRows=1> ... </cfoutput> C. Hatton Humphrey > -----Original Message----- > From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, February 13, 2002 2:31 PM > To: CF-Talk > Subject: Random row from SQL > > > A while back someone posted some code to generate a random number between > two numbers using SQL. I'd like to be able to do that. I want to be able > to return a random row from among rows in an ad database. Does > anyone have > ideas on how this might be accomplished? > > Mark > ______________________________________________________________________ Why Share? Dedicated Win 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=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
