"Select Top" not working with a variable

[pradipto]

Suppose I have a function/procedure where I need to select the Top n rows where the value of n is based by a variable, how I do it.

For instance, the following is NOT working:

Select Top @Num MyValue
From MyTable
Where <some condition>

Any suggestions will be very helpful ...

Thanks.

 

[jhall01]

I believe this should work

declare @tsql varchar(8000)
SET @tsql = 'SELECT TOP ' + @num + 'MyValue FROM mytable where ...'

EXEC(@tsql)

 

[jhall01]

correction

declare @tsql varchar(8000)
SET @tsql = 'SELECT TOP ' + [blue]cast(@num as varchar(10))[/blue] + 'MyValue FROM mytable where ...'

EXEC(@tsql)

 

[donutman]

You need an Order By clause and prefably on a column that is unique.

[Blue]SET[/Blue] [Black]RowCount[/Black] @Top
[Blue]SELECT[/Blue] [Gray]*[/Gray] [Blue]FROM[/Blue] YourTable [Blue]ORDER[/Blue] [Blue]BY[/Blue] YourOrderColumn

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]