Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    2

    Unanswered: Need to Return x rows but Select Top doesn't work with variable

    I am writing some functions that work on a time series database of prices, ie volatility, correlation. I need to use the SELECT TOP syntax, but cannot do this with a variable, ie 'SELECT @x TOP * from prices'. My solution is to simply have a function for each potential period that will be looked at - 30day_volatility, 60day_volatility, etc. I looked at setting the ROWCOUNT variable but this is not allowed in functions. I haven't posted any DDL because I think the question is general enough - How do I return n ordered rows from a function without using SELECT TOP, or is there a way to use SELECT TOP with a variable that I am not aware of.

    Thanks!

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    If I recall correctly from the product demo, this is possible in SQL 2005 (but certainly NOT in 2000, which probably does you no good).

    One alternative might be to write an ordered recordset of data to a temporary table with an IDENTITY column and then select those rows where the IDENTITY value was less than your TOP value. This might work if you can reasonably expect the number of rows to fall within a certain range (in your sample, perhaps 0-120). It would NOT work well if you were dealing with very large recordsets.

    You could potentially write this as a function and pass in the number of rows to return as a parameter of the function.

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Jan 2006
    Posts
    2
    This code is actually in a function, which is why I cannot use 'SET RECORDCOUNT'. The SELECT TOP is in a subquery and from these results the volatility, correlation, etc is computed.

Posting Permissions

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