getting the value in a variable

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

General SQL Server Forums

New to SQL Server Programming

getting the value in a variable

Author

Topic

shakthiA
Starting Member

12 Posts

Posted - 2008-05-07 : 05:03:08

I have a stored procedure that returns the max. number of publication views by author. I will not pass the author name, my stored procedure will select the top 1 like here.

SELECT DISTINCT TOP 1
dbo.FnGetNoOfPublicationViewsByAuthor(C.CUSTOM2) AS 'TotalPublicationsViews',
C.CUSTOM2 AS 'Author'
FROM HDS_CUSTOM C
GROUP BY C.CUSTOM2

I want to get the value of this Author seperately to use in other select statements in the same SP. how can I get it. Is there possibility?

Thanks.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-07 : 05:09:27

[code]Declare @TotalPubs int, @Author varchar(50)

SELECT TOP 1 @TotalPubs = dbo.FnGetNoOfPublicationViewsByAuthor(C.CUSTOM2),
@Author = C.CUSTOM2
FROM HDS_CUSTOM C
GROUP BY C.CUSTOM2[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-07 : 05:11:47

[code]DECLARE @Views int,@Author varchar(50)

SELECT @Views=t.TotalPublicationsViews,
@Author=t.Author
FROM
(SELECT DISTINCT TOP 1
dbo.FnGetNoOfPublicationViewsByAuthor(C.CUSTOM2) AS 'TotalPublicationsViews',
C.CUSTOM2 AS 'Author'
FROM HDS_CUSTOM C
GROUP BY C.CUSTOM2)t[/code]

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-07 : 05:52:11

Why is there no 'order by' clause in these queries? Without it, the 'top 1' row will be 'arbitrary'. Add this to the end of harsh's query...

ORDER BY 1 DESC

I also suspect that you don't need a function for this, and if you move the logic of the function into this query, it will be much quicker (because it won't need to run a separate query for each author). What does your function look like?

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources