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

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.

Solution

You have to enclose the parameter in parenthesis like:

DECLARE @QQ INT = 10

SELECT  TOP (@QQ)
        *

FROM    Your_Table

Comments

[Lamak]

what if you try SELECT TOP(@n)?

[JNK]

Just try it! It would have been faster to actually run the query than to post it here.

[Igor Borisenko]

social.msdn.microsoft.com/search/…

[etm124]

@onedaywhen You are correct, this is basically the same question. Thanks for pointing this out. I don't know the 'rules' regarding this situation. Mod delete this?