use of variables - why doesnt htis work

[duncan]

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

[Hugo Kornelis]

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)

[Dave]

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:<se********************************@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

[Dave]

Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<se********************************@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)