"select TOP" with parameter...

Hi there.
Does anyone know of a way to get the following statement to work without
using dynamic SQL?.....
I'm trying to get a "select TOP" to work while passing in a parameter as 'N'
for 'select the first N rows...'
declare @.NumberofRecords integer
select @.NumberofRecords = 5
select top @.NumberofRecords
from MyTable
Any info would be appreciated!len
Lookup SET ROWCOUNT in the BOL
"len" <len@.discussions.microsoft.com> wrote in message
news:3643D907-3483-4B3D-9F9A-B7D45EABF6AB@.microsoft.com...
> Hi there.
> Does anyone know of a way to get the following statement to work without
> using dynamic SQL?.....
> I'm trying to get a "select TOP" to work while passing in a parameter as
> 'N'
> for 'select the first N rows...'
> declare @.NumberofRecords integer
> select @.NumberofRecords = 5
> select top @.NumberofRecords
> from MyTable
> Any info would be appreciated!|||You are not selecting anything so maybe this will work
select top @.NumberofRecords myField1, myField2, etc.
from MyTable
if that still fails try
EXEC('select top' + @.NumberofRecords + ' myField, myField2, etc. from
MyTable')
HTH,
Gerard|||Len,
you can't use a parameter with TOP in SQL 2000. In 2005 it is possible
see this article for more details:
http://support.microsoft.com/defaul...kb;en-us;891605
In 2000 and 7.0 you can use the SET ROWCOUNT option to achieve what you
want.
Try something like
Declare @.num int
SET @.num = 8
SET ROWCOUNT @.num
select * from myTable
Order by columnname
Markus|||I just did a quick check in Qry Analyzer and
EXEC('select top ' + @.NumberofRecords + ' myField, myField2, etc. from
MyTable')
works, provided offcourse that @.NumberofRecords is a string
Gerard|||"Gerard" <g.doeswijk@.gmail.com> wrote in message
news:1130325402.186088.177800@.g14g2000cwa.googlegroups.com...
>I just did a quick check in Qry Analyzer and
> EXEC('select top ' + @.NumberofRecords + ' myField, myField2, etc. from
> MyTable')
> works, provided offcourse that @.NumberofRecords is a string
It does work, but that's not a parameterised procedure, it's dynamic SQL.
Plenty of reasons to try and avoid using that if possible.
Dan|||> select top @.NumberofRecords myField1, myField2, etc.
> from MyTable
This will fail because TOP does not take parameters in SQL Server 2000!

> EXEC('select top' + @.NumberofRecords + ' myField, myField2, etc. from
> MyTable')
This will fail because (a) @.NumberOfRecords can't implicitly be concatenated
into a string and (b) you didn't leave a space after top, so it will come
out with a syntax error like this:
select top5 myColumn, myColumn2, ...
Please see http://www.sommarskog.se/dynamic_sql.html to understand why
dynamic SQL is not always the best knee-jerk reaction to a problem...|||Yes yes yes,
I know about the limitations/dangers/restrictions with/of dynamic sql,
I was a bit quick of the gun maybe.
And thanks for checking on the punctuation, e.g. the space after the
top.
On your point b)

> works, provided offcourse that @.NumberofRecords is a string
which would, I forgot to mention, require an additional variable and a
CONVERT if the initial variable is an int.