Post reply

How to list top 10 from a stored procedure?

  • 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

  • September 27, 2006 at 4:08 pm

    #662877

    Use @@Rowcount

    _____________
    Code for TallyGenerator

  • September 27, 2006 at 5:57 pm

    #662883

    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.

  • September 28, 2006 at 6:11 am

    #662950

    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

  • September 28, 2006 at 6:16 am

    #662951

    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

     

  • September 28, 2006 at 8:18 am

    #663003

    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.

     

     

  • September 28, 2006 at 8:29 am

    #663010

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

    --Andrew

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

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