Sql server use variable in selection top row

Question

I have a stored procedure that select top X from Table,

declare @i int ;
set @i = 10 ;
select top @i from tableNam

Incorrect syntax near '@i'.

What should i do ?

suppose that the @i comes from parameters in my storedProcedure

Answer 1

Following syntax will work

declare @i int ;
set @i = 10 ;
select top (@i) * from sys.tables

Answer 2

You have to enclose the variable in parenthesis to make it work.

declare @i int ;
set @i = 10 ;
select top (@i) * from tableNam

If you take the cursor to the error line you will find the reason as to why you need the parenthesis like this:

So the value should be a integer. Also if you see the TOP keyword from MSDN then it says to use the parenthesis. The syntax said by MSDN is:

[ 
    TOP (expression) [PERCENT]
    [ WITH TIES ]
]

Answer 3

You can use dynamic query to get the result:

declare @i int ;
set @i = 10;

DECLARE @SqlQury VARCHAR(500) = '';
SET @SqlQury = 'SELECT TOP ' + CAST(@i AS VARCHAR) + ' * FROM tablename';
-- PRINT @SqlQury
EXEC (@SqlQury)