Variable as top value

ORIGINAL QUESTION:

declare @num as int set @num = 5

select top @num col1, col2 from table1

The above does not work. It does not like @num being used that way. What needs to be done so I can have a variable value next to the top command?

It gives the error:

Incorrect syntax near '@num'

开发者_StackOverflow社区

SELECT TOP (@num) a FROM table

SQL server 2005 onwards, this is supported to parameterize TOP.

SQL Server 2000 onwards:

declare @num as int 
set @num = 5  

SET ROWCOUNT @num 

select col1, col2 from table1 

SET ROWCOUNT 0 

UPDATE: Actually, are you sure this doesn't work (I don't have a 2005 instance available):

declare @num as int 
set @num = 5  

select TOP (@num) col1, col2 from table1 
-- Implictly in clustered index order...

In parenthesis

declare @num as int set @num = 5

select top (@num) col1, col2 from table1

You have to wrap the variable in parentheses

declare @num int 

set @num = 5

select top (@num) col1, col2 from table1

Using string concatenation and sp_executesql is one way to achieve that ...

declare @num as int set @num = 5

declare @sql varchar(255)
select @sql = 'select top ' + @num + ' col1, col2 from table1'
sp_executesql @sql

Try this:

DECLARE @num AS INT 
DECLARE @SQL AS NVARCHAR(MAX) 
SET @num = 5  
SET @SQL = N'select top ' + CAST(@num AS VARCHAR(5)) + ' col1, col2 from table1'
EXECUTE sp_executesql @SQL

try using dynamic SQL

something like this

declare @dynamicsql nvarchar(4000);
declare @num as int;
set @num = 5

set @dynamicsql = 'select top ' + convert(nvarchar, @num) + ' col1, col2 from table1';
exec @dynamicsql;

You need to use dynamic SQL, or you can rewrite the entire SQL to use some kind of ROWNUMBER trick, not sure which one will be best performance-wise.

For "dynamic SQL", you need to build your SQL in a string and then execute it.

See here for details.