Using Parameter with Select Top @TopNum

1

For a query like this:
Select Top 1000 Names from People order by Age

Is there a way I can pass the number that I want to show, in as a Parameter.
Select Top @TopNum Names from People order by Age
doesn't work.

Thanks.

2

you can use a stored procedure with parameter ..

create stored procedure SP_Name ... @TopNum Paramater
begin
declare @sql nvarchar(max) = ''
set @sql = 'select top '+@TopNum + ' Names from People Order by Age '
execute(@SQL)
end

to execute stored procedure ..
exec SP_Name (500)

1 Like
3 
declare @TopNum int = 40
declare @query varchar(max)

set @query = 'select top ' + cast(@TopNum as varchar(500)) + ' * from SharedResources.dbo.adGroupMembers'

exec(@query)
1 Like
4

You can directly use a variable. Note that the parentheses around the value is the correct syntax, you should always use them, even with a literal number, such as ... TOP (50) ...:

Declare @TopNum int
Set @TopNum = 1000

Select Top (@TopNum) Names from People order by Age

2 Likes