Return then Top X Rows in RS

Question

Return then Top X Rows in RS

sql_Reporter

SSCrazy

Points: 2329
More actions
April 11, 2008 at 8:54 am

#205693

Hi i'd like to give the user the option to enter in the top X to return the number of rows.
X = a number.
The default will be 100.
Is this possible to do in RS and if so can i get some pointers thanks

Viewing 9 posts - 1 through 8 (of 8 total)

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

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 1

if you're using a query (rather than a stored proc), you only need to create a report parameter and reference that parameter in a TOP clause:

select top(@parm) ... from ...

sql_Reporter SSCrazy Points: 2329 More actions · Answer 2

Hey i tested this and am getting a Error on the @Top paramter it seem to not like the @Top in the Select statement

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 3

top with a variable/parameter is valid SQL:

declare @top int

set @top = 25

select top(@top) * from [some_table]

can you post your query?

sql_Reporter SSCrazy Points: 2329 More actions · Answer 4

I copied your query into my QA and changed the table name and ran the query. It gave me a error

Here is the query

declare @top int

set @top = 25

select top (@top) * from barcode

Here is the Error

Msg 170, Level 15, State 1, Line 3

Line 3: Incorrect syntax near '('.

I know this may seem silly to ask but would the way my SQL server is installed affect not being able to use a top variable. I i was getting this error in my report

'TOP clause Expressions' support not available in this server version.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Are you using SQL Server 2000 or 2005? I think 2000 doesn't support TOP with a variable.

John

sql_Reporter SSCrazy Points: 2329 More actions · Answer 6

Am using 2000 but its on 2005 interface. So is there away around this ?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

You have two options. You can build your SQL string with @top as a parameter and execute it using sp_executesql, or you can use SET ROWCOUNT, which does allow a variable.

John

abhijeetv SSCommitted Points: 1945 More actions · Answer 8

Have you tried something similar to the following already?

declare @cnt int

declare @STR nvarchar(50)

set @cnt=1

set @STR = SELECT TOP ' + Convert(char(1), @cnt) + ' cost FROM '

exec sp_executesql @STR