Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    India
    Posts
    31

    Unanswered: SPROC to select Top N

    hi all

    i need to design a SPROC which will return me top n rows from a table.

    like GetTopN 4, will give me top 4 tuples

    Code:
    /**
    	GetTopN
    	To list top n rows
    	Date - 05 July 2004
    	Yogesh Jangam
    */
    
    IF EXISTS (SELECT name FROM sysobjects 
    		WHERE name = 'GetTopN' AND type = 'P')
     DROP PROCEDURE GetTopN
    GO
    
    /************************ Actual SP Code *********************************/
    Create procedure GetTopN (@intN int)
    AS
    BEGIN
    SET NOCOUNT ON
    declare
    
    SELECT TOP @intN  from Employee -- this part i am not able to write
    end
    is there a answer?
    Thanks
    Do not walk behind me, for I may not lead.
    Do not walk ahead of me, for I may not follow.
    Do not walk beside me, either.
    Just leave me alone.

    Yogesh Jangam
    http://yogeshjangam.blogspot.com

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    u need to use dynamic sql
    /**
    GetTopN
    To list top n rows
    Date - 05 July 2004
    Yogesh Jangam
    */

    IF EXISTS (SELECT name FROM sysobjects
    WHERE name = 'GetTopN' AND type = 'P')
    DROP PROCEDURE GetTopN
    GO

    /************************ Actual SP Code *********************************/
    Create procedure GetTopN (@intN int)
    AS
    BEGIN
    SET NOCOUNT ON
    declare @sql nvarchar(1000)
    set @sql='SELECT TOP '+@intN+' from Employee' -- this part i am not able to
    write
    exec (@sql)
    end



    this should work.

  3. #3
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    Or just use SET ROWCOUNT

    /**
    GetTopN
    To list top n rows
    Date - 05 July 2004
    Yogesh Jangam
    */

    IF EXISTS (SELECT name FROM sysobjects
    WHERE name = 'GetTopN' AND type = 'P')
    DROP PROCEDURE GetTopN
    GO

    /************************ Actual SP Code *********************************/
    Create procedure GetTopN (@intN int)
    AS
    BEGIN
    SET NOCOUNT ON
    declare

    SET ROWCOUNT @intN

    SELECT from Employee -- this part i am not able to write
    end
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •