Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Feb 2007
    Posts
    4

    Unanswered: Using TOP in SELECT with a variable value?

    I'd like to use a stored procedure in my MS SQL database to select just the TOP X records from a table, where X is a parameter of the stored procedure. As in:

    CREATE PROCEDURE [dbo].[GetEmployees]
    @NumRecords INT
    AS
    SELECT TOP @NumRecords EmployeeID, EmployeeName
    FROM [dbo].[Employees]

    The above results in an "Incorrect syntax near '@NumRecords'." error. If I replace @NumRecords in the SELECT TOP with a hard-coded value (such as 2, or 10, or 100), it works.

    Is it possible to use a variable in TOP?

    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What version of SQL Server?

    Also, are you aware that you shouldn't use TOP without an ORDER BY clause, right?
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2007
    Posts
    4
    I'm using MS SQL Server 2005.

    No, I wasn't aware that TOP should be used with ORDER BY. Why is that?

    Thanks

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    TOP cannot accept a variable, so we have to take a slightly different approach:

    2005 solution
    Code:
    --2005
    SELECT *
    FROM   (
            SELECT employee_id
                 , employee_name
                 , Row_Number() OVER (ORDER BY employee_name DESC) As [row_number]
            FROM   dbo.employees
           ) As [x]
    WHERE  row_number <= @num_records
    Because the physical order of database has no meaning, if we do not specify an ORDER for our TOP query, we cannot guarentee the results will be the same every time we execute the same query (even if it may seem that way!)

    I've knocked up a pretty noddy example for this below - same query, different results
    Code:
    IF object_id('dbo.ordering_test') IS NOT NULL
      DROP TABLE dbo.employees
    
    CREATE TABLE dbo.ordering_test (
       employee_id   int
     , employee_name varchar(20)
    )
    
    INSERT INTO dbo.ordering_test(employee_id, employee_name)
          SELECT 1, 'George'
    UNION SELECT 2, 'Rudy'
    UNION SELECT 3, 'Pat'
    UNION SELECT 4, 'Sean'
    UNION SELECT 5, 'Paul'
    UNION SELECT 6, 'Mark'
    UNION SELECT 7, 'Dan'
    
    SELECT *
    FROM   dbo.ordering_test
    
    CREATE CLUSTERED INDEX i1 ON dbo.ordering_test(employee_name)
    
    SELECT *
    FROM   dbo.ordering_test
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2007
    Posts
    4
    That worked perfectly. Thanks for the detailed explanation.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    TOP cannot accept a variable
    Yes it can! http://msdn.microsoft.com/en-us/library/ms189463.aspx

    EDIT - just seen your sample data. LOL!
    Last edited by pootle flump; 09-22-08 at 06:24.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by pootle flump
    EDIT - just seen your sample data. LOL!
    those employees? worst least productive company EVER! the code would be elegant but it would not do anything in particular.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I realised shortly after posting (an hour or so later) that you could indeed use a variable, if you wrapped it in parenthesis!

    Sadly, these chaps beat me to the correction
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Know thy platform
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Thrasymachus
    those employees? worst least productive company EVER! the code would be elegant but it would not do anything in particular.
    There's money in that, I'm sure of it!
    George
    Home | Blog

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Brett Kaiser
    Know thy platform
    to be fair george was working with 6.5 until about six months ago. TOP did not even exist in that.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    do I hear Stand by Your Man by Tammy Wynett playing in the distance?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I claimed the position of Chief George Baiter years ago. Find your own prodigy to goad!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Everyone knows that Pootle Flump is a Master Baiter. The way that he works George is just amazing!

    -PatP

  15. #15
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    heee heee heee heee. yours was better than what I was about to post so I do not mind getting sniped.

    does that violate the new decency rules?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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