March 29, 2009 at 7:16 am
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
March 29, 2009 at 7:20 am
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
March 30, 2009 at 7:22 am
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***----
March 30, 2009 at 2:08 pm
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
March 30, 2009 at 2:21 pm
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
March 30, 2009 at 2:30 pm
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?
March 30, 2009 at 2:39 pm
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
March 30, 2009 at 2:46 pm
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
March 30, 2009 at 2:47 pm
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
March 30, 2009 at 2:48 pm
Thanks Flo - that's what I get for typing this stuff out blindly. Good catch...
You're welcome! 🙂
Greets
King of typos...
March 31, 2009 at 3:46 pm
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