Random Columns - 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)

Random Columns

Author

Topic

fongus
Starting Member

2 Posts

Posted - 2004-05-07 : 14:04:35

Hello, I have a table with this similar design:

ID int
Image1 image
TypeImage1 varchar(20)
Image2 image
TypeImage2 varchar(20)
Image3 image
TypeImage3 varchar(20)

I need to create a stored procedure that will randomly select an image column and its text typeimage column and return those values.

In other words, I don't want to retrieve those three images, just one, and random. It can be either image1 or 2 or 3.

I don't really have a clue how to do this.
Any ideas.

Thanks for your help.

-Gabriel

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-05-07 : 14:12:33

Well, one way would be to generate a random number in your stored proc, and have a big giant select case in there to run a different select statement depending on the random number.

Something tells me there's a better way though. If you had your images in multiple rows instead of columns there's a MUCH easier way.

http://www.sqlteam.com/item.asp?ItemID=8747

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-05-07 : 14:17:38

declare @sqlstatement varchar(8000)
declare @num int

set @num = rand() * 3

CASE when @num = 0 then set @sqlstatement = 'SELECT Image1 from table '
when @num = 1 then set @sqlstatement = 'SELECT Image2 from table '
when @num = 2 then set @sqlstatement = 'SELECT Image3 from table ' end

exec(@sqlstatement)

Duane.

fongus
Starting Member

2 Posts

Posted - 2004-05-07 : 14:22:50

Thank you MichaelP and ditch a lot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources