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.
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 INTSET @ROWS = 10SELECT TOP @ROWS * FROM PRCISRServer: Msg 170, Level 15, State 1, Line 5Line 5: Incorrect syntax near '@ROWS'.The query works fine when the number of rows is hard coded:SELECT TOP 10 * FROM PRCISRAny ideas?" |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-02-13 : 08:55:47
|
You need to make use of Set RowCountDECLARE @ROWS INTSET @ROWS = 10SET ROWCOUNT @ROWSSELECT * FROM PRCISRSET ROWCOUNT 0MadhivananFailing to plan is Planning to fail |
|
|
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. |
|
|
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 @VarEXEC('SELECT TOP '+@ROWS+' * FROM PRCISR')MadhivananFailing to plan is Planning to fail |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2006-02-13 : 09:35:35
|
My bad, that's what I wanted to say. |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-02-13 : 09:44:14
|
So, SQL Server 2005 also allows top @var as Dynamic?MadhivananFailing to plan is Planning to fail |
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2006-02-13 : 10:13:04
|
in sql 2005 you can use:declare @n intset @n = 100select top @n ...from ... That's what I've read, I havent played with 2005 yet. |
|
|
|
|
|
|
|