Are you able to pass the 'TOP' number as a parameter to a stored procedure? [duplicate]

Question

Possible Duplicate:
SQL Server - use a parameter to select the top X of the result set

My query in my stored procedure looks something like:

select top 9 from my_table; 

I'd like to edit the stored procedure to dynamically produce the limit from a parameter, however, this does not seem to be working:

ALTER PROCEDURE [dbo].[my_stored_procedure]    @n INT(2) AS BEGIN  SELECT TOP @n from my_table; 

Is this doable? Or do I have to do something like:

@n int(2), @sql varchar(30)  @sql = 'select top ' + @n '* from my table'; exec(@sql); 

Thanks.

Answer 1

You have to enclose the parameter in parenthesis like:

DECLARE @QQ INT = 10  SELECT  TOP (@QQ)         *  FROM    Your_Table