Stored procedure and 8 random images

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

SQL Server Development (2000)

Stored procedure and 8 random images

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-10 : 08:53:50

Jon writes "


This is what I have so far.


CREATE PROCEDURE Random_Images 
---
(@Rand_1 int = 0,
@Rand_2 int = 0,
@Rand_3 int = 0,
@Rand_4 int = 0,
@Rand_5 int = 0,
@Rand_6 int = 0,
@Rand_7 int = 0,
@Rand_8 int = 0,
@Random int=0,
@i int=0,
@MIN int = 0,
@MAX int = 0)
---
AS

---
SET @MIN = (SELECT MIN(pID) FROM Products)
SET @MAX = (SELECT MAX(pID) FROM Products)

-- ** Guessing from here to

LOOP
SET @Random = (rand()* (@MAX - @MIN)) + @MIN

IF @Random = (SELECT pID FROM Products WHERE pID =  @Random AND Show = 'YES')
SET @i = @i + 1
SET @Rand_(@i) = @Random
END IF

IF @i = 8
EXIT
END IF

END LOOP

-- ** Here

SELECT * FROM Products

WHERE pID IN (@Rand_1,@Rand_2,@Rand_3,@Rand_4,@Rand_5,@Rand_6,@Rand_7,@Rand_8)
GO
---
Am I close or off the mark?
Thank you for any help.

nr
SQLTeam MVY

12543 Posts

Posted - 2002-04-10 : 09:27:03

SET @Rand_(@i) = @Random

will be a problem.
You are trying to use it like an array which doesn't exist in sql server.
Make it a temp table with a single row, insert the random nos and use the temp table in the in clause.
(I haven't looked at what you're trying to do just how you're doing it).

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-10 : 14:47:50

You might want to read this article on random rows by Graz.

------------------------
GENERAL-ly speaking...

motokevin
Starting Member

36 Posts

Posted - 2002-04-10 : 19:37:07

Dude,

If you want 8 random records/products/images to show then try this:

Select Top 8 *
From Products
Where Show = 'Yes'
Order By NewID()

jpchambers
Starting Member

3 Posts

Posted - 2002-04-10 : 21:16:58

Thank you for the ideas.

nr - I will try a that and re-post.

AjarnMark - I read it thanks.

motokevin - Will the code take a random ID and sort down from there. I have multiple products from multiple companies. It needs to pick and choose from the entire range of ID's. Is this possible with your code?

jc

motokevin
Starting Member

36 Posts

Posted - 2002-04-10 : 21:31:42

By using:

ORDER BY NewID()

You can select from any range of columns and the order will change each time.

Try it. I'm sure you'll like it.

jpchambers
Starting Member

3 Posts

Posted - 2002-04-11 : 00:30:35

I will try it now.

jc

jpchambers
Starting Member

3 Posts

Posted - 2002-04-11 : 17:01:17

Well both the solutions seem to work.
motokevin - have you noticed any patterns with NewID()?
(you were right an easy solution)

Here is the code for the second way.
Note: I was unable to figure out CASE so I used IF...END instead.

CREATE PROCEDURE Random_Images2

(@Rand_1 int = 0,
@Rand_2 int = 0,
@Rand_3 int = 0,
@Rand_4 int = 0,
@Rand_5 int = 0,
@Rand_6 int = 0,
@Rand_7 int = 0,
@Rand_8 int = 0,
@Random int=0,
@i int=0,
@MIN int = 0,
@MAX int = 0)

AS

SET @MIN = (SELECT MIN(pID) FROM Products)
SET @MAX = (SELECT MAX(pID) FROM Products)

CREATE TABLE #TmpTbl (StyleID varchar(12) NULL )
INSERT INTO #TmpTbl VALUES(0)

WHILE @i <> 8
BEGIN
SET @Random = (rand()* (@MAX - @MIN)) + @MIN

IF @Random = (SELECT pID FROM Products WHERE pID = @Random AND Show = 'YES')
BEGIN
SET @i = @i + 1
UPDATE #TmpTbl SET StyleID = @Random
END

IF @i = 1
BEGIN
SET @Rand_1 = (SELECT StyleID FROM #TmpTbl)
END

IF @i = 2
BEGIN
SET @Rand_2 = (SELECT StyleID FROM #TmpTbl)
END

IF @i = 3
BEGIN
SET @Rand_3 = (SELECT StyleID FROM #TmpTbl)
END

IF @i = 4
BEGIN
SET @Rand_4 = (SELECT StyleID FROM #TmpTbl)
END

IF @i = 5
BEGIN
SET @Rand_5 = (SELECT StyleID FROM #TmpTbl)
END

IF @i = 6
BEGIN
SET @Rand_6 = (SELECT StyleID FROM #TmpTbl)
END

IF @i = 7
BEGIN
SET @Rand_7 = (SELECT StyleID FROM #TmpTbl)
END

IF @i = 8
BEGIN
SET @Rand_8 = (SELECT StyleID FROM #TmpTbl)
END

END

DROP TABLE #TmpTbl

SELECT * FROM Products

WHERE pID IN (@Rand_1, @Rand_2, @Rand_3, @Rand_4, @Rand_5, @Rand_6, @Rand_7, @Rand_8)
GO

Is this try better?
Thank you all.
jc

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-04-11 : 20:49:45

You learn something new everyday.

NewID() is pretty neat and works cool in this situation when you want to get random records from a database.

Graz - you should add that into this articles (http://www.sqlteam.com/item.asp?ItemID=896)

depending on the number of records you want you TOP statement would then reflect that.

Very nice solution motokevin

Got SQL?

motokevin
Starting Member

36 Posts

Posted - 2002-04-11 : 20:58:12

Pulling a random recordset is one of those things that you spend hours trying to solve, looking through book after book of SQL and hounding the search engines for a simple solution. Only to find Max...Min...Ave...Count...Blah...Blah...etc...

I finally found this.

Wish I could say I thought of it first.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources