Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Unexpected error with global variable

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-02-13 : 08:49:08
Michael writes "I'm hitting an error with this query related to the global:

/* Retrieve a specific number of rows from a table */
DECLARE @ROWS INT
SET @ROWS = 10

SELECT TOP @ROWS * FROM PRCISR

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near '@ROWS'.


The query works fine when the number of rows is hard coded:
SELECT TOP 10 * FROM PRCISR

Any ideas?"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-13 : 08:55:47
You need to make use of Set RowCount

DECLARE @ROWS INT
SET @ROWS = 10
SET ROWCOUNT @ROWS
SELECT * FROM PRCISR
SET ROWCOUNT 0

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-13 : 09:14:41
Sure, you can use variable in top cluase on mssql 2000, but you can on mssql server 2005.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-13 : 09:21:29
quote:
Originally posted by mmarovic

Sure, you can use variable in top cluase on mssql 2000, but you can on mssql server 2005.


Did you mean this?
Sure, you can't use variable in top cluase on mssql 2000, but you can on mssql server 2005.

But in SQL Server 2000, Dynamic SQL Allows Top @Var

EXEC('SELECT TOP '+@ROWS+' * FROM PRCISR')



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-13 : 09:35:35
My bad, that's what I wanted to say.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-13 : 09:44:14
So, SQL Server 2005 also allows top @var as Dynamic?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-13 : 10:13:04
in sql 2005 you can use:
declare @n int
set @n = 100
select top @n ...
from ...
That's what I've read, I havent played with 2005 yet.
Go to Top of Page
   

- Advertisement -