Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    7

    Unanswered: How to pass Top a value dynamically

    Hi Guys: I am trying to get a top parameter dynamically but it does not seem to work

    SELECT TOP
    (SELECT COUNT(DISTINCT RegionID) AS COUNT
    FROM Sales)
    *
    FROM Sales

    but when I hard code the value it works


    SELECT TOP
    2
    *
    FROM Sales

    any ideas...Thanks
    Last edited by attarid; 05-19-04 at 15:39.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You'll have to include it using dynamic SQL.

    -PatP

  3. #3
    Join Date
    May 2004
    Posts
    7
    Can you please show me how
    Thanks

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    DECLARE @foo VARCHAR(10)
    
    SELECT @foo = CAST(Count(DISTINCT regionID) AS VARCHAR(10)) FROM Sales
    EXECUTE ('SELECT TOP ' + @foo + ' * FROM Sales')
    -PatP

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    EDIT: Damn...sniped...by a long shot...

    Code:
    USE Northwind
    
    DECLARE @x int, @SQL varchar(8000)
    
    SELECT @x = COUNT(DISTINCT CustomerId) FROM Orders
    
    SELECT @SQL = 'SELECT TOP ' + CONVERT(varchar(10),@x) + '* FROM Orders'
    
    EXEC(@SQL)
    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.

  6. #6
    Join Date
    May 2004
    Posts
    7
    Thanks a lot Pat .. I really appreciate it

  7. #7
    Join Date
    May 2004
    Posts
    7
    Thanks Brett

Posting Permissions

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