How to list top 10 from a stored procedure?

Question

Post reply

How to list top 10 from a stored procedure?

jenniferhu46

SSCrazy

Points: 2990
More actions
September 27, 2006 at 4:05 pm

#192393

Hi experts,
I have a stored procedure as following, so how to select top 10 store_num from this sp? Thank you.
-----------------------------

CREATE Procedure sp_CommunityStoreList
(
@ZipCode Numeric(5, 0) = Null
   )
As
set nocount on

Declare @Latitude Float(10)
Declare @Longitude Float(10)
-- Lookup longitude, latitude for zip codes
Select @Latitude = Latitude, @Longitude = Longitude From Zips_Range_Table Where Zip_Code = @ZipCode

Select s.Store_Num,s.Store_Address, cast(( 3958.75 * ACos(Sin(@Latitude/57.2958)* Sin(Latitude/57.2958) + Cos(@Latitude/57.2958) * Cos(Latitude/57.2958) * Cos(Longitude/57.2958 - @Longitude/57.2958)))as decimal(10,2)) as Distance
From
Zips_Range_Table z, InSightAmKgODS.micros.Store_Table s
Where
Longitude Is Not Null
And Latitude Is Not Null
And (
   3958.75 * ACos(Sin(@Latitude/57.2958) *
   Sin(Latitude/57.2958) +
   Cos(@Latitude/57.2958) *
   Cos(Latitude/57.2958) *
   Cos(Longitude/57.2958 - @Longitude/57.2958))   ) <=20
And z.zip_code=s.zip_code and s.co_code=1 and s.store_mgr is not null
Group by s.zip_Code, s.store_num, s.store_address,s.city,s.state,cast(( 3958.75 * ACos(Sin(@Latitude/57.2958)* Sin(Latitude/57.2958) + Cos(@Latitude/57.2958) * Cos(Latitude/57.2958) * Cos(Longitude/57.2958 - @Longitude/57.2958)))as decimal(10,2))
order by distance
return
GO

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Use @@Rowcount

_____________
Code for TallyGenerator

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 2

I think he means ROWCOUNT, not the @@ROWCOUNT system variable. To get the top 10 rows you'd do:

SET ROWCOUNT 10

EXEC sp_CommunityStoreList

-----------------------

Oh, it's also generally bad format to use "sp_" as a prefix for your procedure names. It causes the server to scan the master database catalogues first before the current database and can cause problems. "sp_" technically refers to System Procedure.

Casper Leon Nielsen SSC Enthusiast Points: 125 More actions · Answer 3

This functionality is IMHO not to be implemented in a stored procedure at all: As it have no side-effects it should be programmed as a function. Doing this will give you the ability to SELECT from it directly.

Best regards,

Casper Nielsen

Casper Leon Nielsen SSC Enthusiast Points: 125 More actions · Answer 4

Furthermore naming your stored procedure as sp_[text] will indicate to the sql engine that it is a system stored procedure which is unintended here I guess.

For a more comprehensive guide to programming the sql server 2005 you can check out my http://www.codeproject.com/useritems/T-SQL_Coding_Standard.asp

Dan Murray Right there with Babe Points: 779 More actions · Answer 5

That's a complex subquery you have there but I have to wonder why you wouldn't use 'Top' to get you results, i.e.

Select TOP 10 s.Store_Num,s.Store_Address, cast(( 3958.75 * ACos(Sin(@Latitude/57.2958)* Sin(Latitude/57.2958) + Cos(@Latitude/57.2958) * Cos(Latitude/57.2958) * Cos(Longitude/57.2958 - @Longitude/57.2958)))as decimal(10,2)) as Distance

There is also the possibility that there might be more the one store in this lat/long range, (Where I live for years there where two Vons stores directly across the street from one another. In this case the top ten range might include eleven stores).

In that case using a subquery to return the top ten lat/long and then selecting all the stores with those lat/long values would do the trick.

Andrew in WV SSCommitted Points: 1655 More actions · Answer 6

You beat me to it, Dan. I was just going to suggest using the TOP keyword.

--Andrew