using sp_executesql with top variable in sql statement

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

SQL Server Development (2000)

using sp_executesql with top variable in sql statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-12 : 07:46:46

alexia writes "I'm using sp_executeSQL to run a query like this :
declare @parm nvarchar(500)
declare @sql nvarchar(5000)
declare @upper int

set @upper = 10
set @parm=N'@upperX int'
set @sql = N'select top @upperX field1 from tableX'

execute sp_executesql @sql,@parm,@upperX = @upper

But it can't work out. Is there anyone know how using top variable in sql statement when using sp_executeSQL ?

thank you very much..."

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-12 : 08:51:29

set @sql = N'select top ' + @upperX ' + field1 from tableX'

you can also use
set rowcount @upperX
and with that you don't need dynamic sql or the top statemnt.
read about it in books online = BOL = sql server help.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources