SELECT TOP Problem - SQL Server Forums

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)

SELECT TOP Problem

Author

Topic

alperozgur
Starting Member

16 Posts

Posted - 2005-03-03 : 07:50:22

Hi;
Here is my problem. I have a query as below :
declare @SAYI AS INT
USE SUNDB
SELECT @SAYI=count(btl_date) FROM BPG_DATES WHERE period=2 and btl_date=1 and year(n_date)=2004

USE SUNDB1
SELECT TOP @SAYI * FROM BPG_DATES WHERE period=2 and btl_date=1 and year(n_date)=2005
GO
when i run this query it gives the error : Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '@SAYI'.

need help urgent...

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-03 : 07:56:09

you can't use the top operator in this way.
you can do however:

set rowcount @SAYI -- set the number of rows to be processed/returned
SELECT * FROM BPG_DATES WHERE period=2 and btl_date=1 and year(n_date)=2005
set rowcount 0 -- set it back to return all

Go with the flow & have fun! Else fight the flow

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-03 : 10:30:05

quote:
Originally posted by spirit1

you can't use the top operator in this way.
you can do however:

set rowcount @SAYI -- set the number of rows to be processed/returned
SELECT * FROM BPG_DATES WHERE period=2 and btl_date=1 and year(n_date)=2005
set rowcount 0 -- set it back to return all

Go with the flow & have fun! Else fight the flow

Mladen,
Don't you mean this?:

set rowcount=@SAYI -- set the number of rows to be processed/returned
SELECT * FROM BPG_DATES WHERE period=2 and btl_date=1 and year(n_date)=2005
set rowcount 0 -- set it back to return all

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-03 : 10:44:28

Are you doubting the flawless syntaxity of Spirit1?!?

try these 2 statements and see which one works:
set rowcount 1
set rowcount = 1

Be One with the Optimizer
TG

Xerxes
Aged Yak Warrior

666 Posts

Posted - 2005-03-03 : 10:50:10

Mladen & TG.....

I am wrong

. Mladen's syntaxity

was flawless

.

Oooops.

Semper fi,

XERXES, USMC(Ret.)
------------------------------------------------------
The Marine Corps taught me everything but SQL!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-03 : 11:03:30

heh....

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-04 : 10:41:20

quote:
Originally posted by alperozgur

Hi;
Here is my problem. I have a query as below :
declare @SAYI AS INT
USE SUNDB
SELECT @SAYI=count(btl_date) FROM BPG_DATES WHERE period=2 and btl_date=1 and year(n_date)=2004

USE SUNDB1
SELECT TOP @SAYI * FROM BPG_DATES WHERE period=2 and btl_date=1 and year(n_date)=2005
GO
when i run this query it gives the error : Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '@SAYI'.

need help urgent...

Try this

declare @SAYI AS INT
USE SUNDB
SELECT @SAYI=count(btl_date) FROM BPG_DATES WHERE period=2 and btl_date=1 and year(n_date)=2004

USE SUNDB1
Exec('SELECT TOP '+@SAYI+' * FROM BPG_DATES WHERE period=2 and btl_date=1 and year(n_date)=2005')
GO

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources