output variable from random query

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)

output variable from random query

Author

Topic

namyst
Starting Member

4 Posts

Posted - 2005-06-16 : 11:16:55

The following statement selects a random row from a record set:

create proc publicPoemOfTheDay

as

SELECT top 1 PoemID, PoemName, Author, PoemText, ImageType, cast(PoemID as varchar)+ '.' + ImageType as PoemIllustration

FROM dbo.Poems

where PoemOfTheDay=1 and PoemArchive=0

ORDER By NEWID()

Is there a way to return the primary key "PoemID" from the results?

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-16 : 11:37:40

I think this should do it:


create proc publicPoemOfTheDay

@PoemID int output

as

select top 1
	@PoemID = PoemID
FROM	dbo.Poems
where	PoemOfTheDay=1 
and	PoemArchive=0
ORDER By NEWID()

SELECT	PoemID
	,PoemName
	,Author
	,PoemText
	,ImageType
	,cast(PoemID as varchar)+ '.' + ImageType as PoemIllustration
FROM dbo.Poems
where PoemID = @PoemID

go

declare @pid int
exec publicPoemOfTheDay @PoemID = @pid output
select @pid

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources