Using a Variable with Top N Percent 1

[DiggerDog]

Hi

I wish to select the top N percent from a table (into a temp table called #Results) where N is a variable passed to the Stored Proc.

Using the code as follows works fine

Select Top 5 Percent into #Results from MyTable

but if try

Declare @N as Int

Select @N = 5

Select Top @N Percent #Results from MyTable

I get and error

Is there any way I can use a variable to specify the % I wish to return

thanks

 

[vongrunt]

AFAIK no, because execution plan depends greatly on percentage.

Without dynamic SQL you can eventually simulate TOP N with SET ROWCOUNT, but this trick has no effects on exec plan and is recommended only as a safety measure.

 

[zhavic]

you can use diynamic query:

Declare @N as Int
Declare @cSelect nvarchar(100)

Select @N = 5

SET @cSelect = 'SELECT TOP ' + CAST( @N AS varchar ) + ' PERCENT * INTO #Results from MyTable'

EXECUTE( @cSelect )

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.