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.

Just try it! It would have been faster to actually run the query than to post it here. — JNK, Jan 06 '12 at 14:11
http://social.msdn.microsoft.com/search/en-us?query=top&x=0&y=0 — Igor Borisenko, Jan 06 '12 at 14:19
@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? — etm124, Jan 06 '12 at 14:39

score 59 · Accepted Answer · answered Jan 06 '12 at 14:12

59

You have to enclose the parameter in parenthesis like:

DECLARE @QQ INT = 10

SELECT  TOP (@QQ)
        *

FROM    Your_Table

answered Jan 06 '12 at 14:12

Gixonita

1 Answers1