How to limit number of records using a parameter in a query

  • Hello:

    I'm familiar with the TOP command and that works fine to limit my record count, using a fixed value. However, I can't get it to work if I try to use it as a parameter. Ultimately this will be a parameter chosen in SSRS, but I 'think' it's more of a query thing than a SSRS thing.

    Any ideas?

    select top (@Limiter) *

    from myTable

    thanks

  • That syntax works fine in SQL. If it's not working in the SSRS query interface, maybe create a stored procedure that takes the parameter and call that from SSRS

    declare @i int

    set @i = 10

    select top (@i) * from sys.objects

    Returns 10 objects

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I have to agree, it looks like a stored procedure may be your only way to get that job done.

    Which frankly is too bad, as I can certainly see where you would have rights to report off a data source, but not to create sprocs on it... which just makes life that much more complicated.

    The SSRS query parser really does NOT seem to appreciate there being a variable there.

    I wonder if it dislikes ALL variables "above" the FROM line in the SELECT statement...?

    Either way I messed around with it for a few minutes trying different approaches, but SSRS kicks it back each time saying there is a syntax error so it won't even try to submit the query.

    (@TopNum is the parm I used)

    ----****ERROR***----

    TITLE: Microsoft Report Designer

    ------------------------------

    An error occurred while executing the query.

    Line 5: Incorrect syntax near '@TopNum'.

    ------------------------------

    ADDITIONAL INFORMATION:

    Line 5: Incorrect syntax near '@TopNum'. (Microsoft SQL Server, Error: 170)

    ----****ERROR***----

  • What you can do in SSRS is:

    1) Create a parameter for your report - let's call it TopNum

    2) In your Dataset, do the following:

    DECLARE @Num int;

    SET @Num = @TopNum;

    SELECT TOP (@Num) FROM dbo.YourTable;

    3) Run it - you should get prompted for the report parameter @TopNum which will be used in the query.

    As long as you are running against a 2005/2008 database, this should work just fine.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams (3/30/2009)


    What you can do in SSRS is:

    1) Create a parameter for your report - let's call it TopNum

    2) In your Dataset, do the following:

    DECLARE @Num int;

    SET @Num = @TopNum;

    SELECT TOP (@Num) FROM dbo.YourTable;

    3) Run it - you should get prompted for the report parameter @TopNum which will be used in the query.

    As long as you are running against a 2005/2008 database, this should work just fine.

    Just a little typo in step 2):

    SELECT TOP (@Num) * FROM dbo.YourTable;

    or

    SELECT TOP (@Num) col1, col2, colN FROM dbo.YourTable;

    Greets

    Flo

  • That is EXACTLY what I tried, but it failed.

    HOWEVER, I was running it against an SQL Server 2000 data source.

    That is VERY interesting to know, thank you for that!

    I hadn't thought to take the version of SQL Server into account when I tried.

    What exactly is the new feature 2005 offers that allows that to be supported over 2000?

  • Florian Reischl (3/30/2009)


    Just a little typo in step 2):

    SELECT TOP (@Num) * FROM dbo.YourTable;

    or

    SELECT TOP (@Num) col1, col2, colN FROM dbo.YourTable;

    Greets

    Flo

    Thanks Flo - that's what I get for typing this stuff out blindly. Good catch...

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • For SQL Server 2000 this should work (replaces step 2):

    DECLARE @Num int;

    SET @Num = @TopNum;

    SET ROWCOUNT @Num

    SELECT * FROM dbo.YourTable;

    SET ROWCOUNT 0

    Not as proper as TOP(x) but works.

    Greets

    Flo

  • Maxer (3/30/2009)


    That is EXACTLY what I tried, but it failed.

    HOWEVER, I was running it against an SQL Server 2000 data source.

    That is VERY interesting to know, thank you for that!

    I hadn't thought to take the version of SQL Server into account when I tried.

    What exactly is the new feature 2005 offers that allows that to be supported over 2000?

    SQL Server 2005 introduced the ability to pass a parameter to TOP. If you are going to run against a SQL Server 2000 system, you can do something like:

    ="SELECT TOP " & @TopNum & " * FROM dbo.YourTable;"

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thanks Flo - that's what I get for typing this stuff out blindly. Good catch...

    You're welcome! 🙂

    Greets

    King of typos...

  • Just for informational purposes, there is a separate forum for SQL 7/SQL 2000 queries. When you post in SQL2k5 the assumption is that you have access to the neat new features in SQL 2005. It helps everybody get it right the first time. 🙂

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 11 posts - 1 through 10 (of 10 total)

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