using TOP in dynamic SQL 2

[CompCodeBoy]

Declare @number int
set @number=10
DECLARE @strSQL varchar(1200)

set @strSQL = ' select top ('+ @number + ') account from contact'

EXEC(@strSQL)

The error I get is :
Conversion failed when converting the varchar value ' select top (' to data type int.

Any help would be appreciated

 

[gmmastros]

Declare @number int
set @number=10
DECLARE @strSQL varchar(1200)

set @strSQL = ' select top (' + [!]Convert(VarChar(20), [/!]@number[!])[/!] + ') account from contact'

EXEC(@strSQL)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LNBruno]

try this:

set @strSQL = ' select top '+ convert(varchar,@number) + ' account from contact'


< M!ke >
"Believe, but verify.

 

[LNBruno]

Dang! I'm getting slow in my old age! ;-)

< M!ke >
"Believe, but verify.

 

[CompCodeBoy]

Thanks a bunch for both of you. Both worked!

 

[LNBruno]

I should win, George, 'cause I took out the parens (two less keystrokes!).

< M!ke >
"Believe, but verify.

 

[sunila7]

I think you can avoid dynamic SQL in this scenario.

Declare @number int
set @number = 10
SET ROWCOUNT  @number
select account from contact

Also if you are using SQL 2005 you can use TOP with variables

Declare @number int
set @number = 10
select TOP (@number) account from contact

Sunil

 

[LNBruno]

And with the ROWCOUNT, specify SET ROWCOUNT 0 to set this option off so that all rows are returned again.

< M!ke >
"Believe, but verify.

 

[CompCodeBoy]

I'm using SQL 2005. That's good to know. Thanks