I am using SQL Server 2000

This doesn't work :-

DECLARE @topnum AS INT
SET @topnum = 2

SELECT TOP @topnum *
FROM tblMyTable

I also tried :-

DECLARE @topnum AS INT
SET @topnum = 2

SELECT TOP (SELECT @topnum) *
FROM tblMyTable

but this does :-

SELECT TOP 2 *
FROM tblMyTable


Why? How can I use a variable to control the number of rows returned
by the TOP key word?

I need to avoid using the ROWCOUNT setting otherwise I may have to
resort to a CURSOR god forbid!


Duncan

On 24 Apr 2004 12:26:36 -0700, duncan wrote:

>I am using SQL Server 2000
>
>This doesn't work :-
>
>DECLARE @topnum AS INT
>SET @topnum = 2
>
>SELECT TOP @topnum *
>FROM tblMyTable
>
>I also tried :-
>
>DECLARE @topnum AS INT
>SET @topnum = 2
>
>SELECT TOP (SELECT @topnum) *
>FROM tblMyTable
>
>but this does :-
>
>SELECT TOP 2 *
>FROM tblMyTable
>
>
>Why? How can I use a variable to control the number of rows returned
>by the TOP key word?

You can't. The TOP clause accepts only an integer constant.


>I need to avoid using the ROWCOUNT setting

Why? What's wrong with using ROWCOUNT?

I admit that it's proprietary syntax, not portable to other DB's. But
so is the TOP clause, so what's the difference?

>otherwise I may have to
>resort to a CURSOR god forbid!

No you don't. Even without TOP *and* without ROWCOUNT, there still are
other options available. The only difference is that the other options
do require you to properly specify what you want.

Lets' get back to your example:

SELECT TOP 2 *
FROM MyTable

So, you're asking for the "first" 2 rows from MyTable. But "first" by
what definition? Tables in the relational model are by definition an
*UN*ordered collection of rows - so unless you specify an order by
clause, the results of this query are undefined. The only thing you
can rely on is that you'll get two rows from MyTable - but which rows?

As soon as you specify the order, there's also an ANSI-standard
alternative without TOP or ROWCOUNT - and one that will work with a
variable. E.g. the following

SELECT TOP 2 *
FROM MyTable
ORDER BY MySortColumn

is equivalent to

DECLARE @topnum int
SET @topnum = 2
SELECT *
FROM MyTable AS MT1
WHERE (SELECT COUNT(*)
FROM MyTable AS MT2
WHERE MT2.MySortColumn < MT1.MySortColumn) < @topnum

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

When using variables in a statement you must encapsulate the entire
statement in another variable itself and then execute that statement.
For example:

declare @topnum as varchar, @command as varchar(1000)
set @topnum = 2

set @command = 'select top ' + @topnum + ' * from tblMyTable'

exec (@command)




Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<seql80hmh3jh3683npbjdltbaggcuh3eke@4ax.com>. ..
> On 24 Apr 2004 12:26:36 -0700, duncan wrote:
>
> >I am using SQL Server 2000
> >
> >This doesn't work :-
> >
> >DECLARE @topnum AS INT
> >SET @topnum = 2
> >
> >SELECT TOP @topnum *
> >FROM tblMyTable
> >
> >I also tried :-
> >
> >DECLARE @topnum AS INT
> >SET @topnum = 2
> >
> >SELECT TOP (SELECT @topnum) *
> >FROM tblMyTable
> >
> >but this does :-
> >
> >SELECT TOP 2 *
> >FROM tblMyTable
> >
> >
> >Why? How can I use a variable to control the number of rows returned
> >by the TOP key word?
>
> You can't. The TOP clause accepts only an integer constant.
>
>
> >I need to avoid using the ROWCOUNT setting
>
> Why? What's wrong with using ROWCOUNT?
>
> I admit that it's proprietary syntax, not portable to other DB's. But
> so is the TOP clause, so what's the difference?
>
> >otherwise I may have to
> >resort to a CURSOR god forbid!
>
> No you don't. Even without TOP *and* without ROWCOUNT, there still are
> other options available. The only difference is that the other options
> do require you to properly specify what you want.
>
> Lets' get back to your example:
>
> SELECT TOP 2 *
> FROM MyTable
>
> So, you're asking for the "first" 2 rows from MyTable. But "first" by
> what definition? Tables in the relational model are by definition an
> *UN*ordered collection of rows - so unless you specify an order by
> clause, the results of this query are undefined. The only thing you
> can rely on is that you'll get two rows from MyTable - but which rows?
>
> As soon as you specify the order, there's also an ANSI-standard
> alternative without TOP or ROWCOUNT - and one that will work with a
> variable. E.g. the following
>
> SELECT TOP 2 *
> FROM MyTable
> ORDER BY MySortColumn
>
> is equivalent to
>
> DECLARE @topnum int
> SET @topnum = 2
> SELECT *
> FROM MyTable AS MT1
> WHERE (SELECT COUNT(*)
> FROM MyTable AS MT2
> WHERE MT2.MySortColumn < MT1.MySortColumn) < @topnum
>
> Best, Hugo

Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<seql80hmh3jh3683npbjdltbaggcuh3eke@4ax.com>. ..
> On 24 Apr 2004 12:26:36 -0700, duncan wrote:
>
> >I am using SQL Server 2000
> >
> >This doesn't work :-
> >
> >DECLARE @topnum AS INT
> >SET @topnum = 2
> >
> >SELECT TOP @topnum *
> >FROM tblMyTable
> >
> >I also tried :-
> >
> >DECLARE @topnum AS INT
> >SET @topnum = 2
> >
> >SELECT TOP (SELECT @topnum) *
> >FROM tblMyTable
> >
> >but this does :-
> >
> >SELECT TOP 2 *
> >FROM tblMyTable
> >
> >
> >Why? How can I use a variable to control the number of rows returned
> >by the TOP key word?
>
> You can't. The TOP clause accepts only an integer constant.
>
>
> >I need to avoid using the ROWCOUNT setting
>
> Why? What's wrong with using ROWCOUNT?
>
> I admit that it's proprietary syntax, not portable to other DB's. But
> so is the TOP clause, so what's the difference?
>
> >otherwise I may have to
> >resort to a CURSOR god forbid!
>
> No you don't. Even without TOP *and* without ROWCOUNT, there still are
> other options available. The only difference is that the other options
> do require you to properly specify what you want.
>
> Lets' get back to your example:
>
> SELECT TOP 2 *
> FROM MyTable
>
> So, you're asking for the "first" 2 rows from MyTable. But "first" by
> what definition? Tables in the relational model are by definition an
> *UN*ordered collection of rows - so unless you specify an order by
> clause, the results of this query are undefined. The only thing you
> can rely on is that you'll get two rows from MyTable - but which rows?
>
> As soon as you specify the order, there's also an ANSI-standard
> alternative without TOP or ROWCOUNT - and one that will work with a
> variable. E.g. the following
>
> SELECT TOP 2 *
> FROM MyTable
> ORDER BY MySortColumn
>
> is equivalent to
>
> DECLARE @topnum int
> SET @topnum = 2
> SELECT *
> FROM MyTable AS MT1
> WHERE (SELECT COUNT(*)
> FROM MyTable AS MT2
> WHERE MT2.MySortColumn < MT1.MySortColumn) < @topnum
>
> Best, Hugo


Try this:

declare @topnum as varchar, @command as varchar(1000)
set @topnum = 2

set @command = 'select top ' + @topnum + ' * from updates'

exec (@command)