Finding the Nth Maximal Value

create proc max_nth_value (@tablename varchar(50),
                           @column varchar(50),
                           @n int,
                           @res decimal (10,4) OUTPUT)
AS
set nocount on
declare @sqlStatment varchar(200)
set @sqlStatment  = 'select a.' + @column + ' from ' + @tableName + ' a ' + 
                   'where ' + convert (varchar(10),@n) + 
                   '=(select count(distinct ' + @column + ')' +
                   ' from  ' + @tableName + ' b ' +
                   ' where ' + 'a.'+ @column +  ' <= ' + 'b.' + @column + ')'
create table #tres (x decimal (10,4))
insert into #tres exec (@sqlStatment)
if @@rowcount = 0  print 'No value found!' else select @res = x from #tres
set nocount off
go

-- usage
declare @res decimal (10,4)
exec max_nth_value 'products','unitprice',8,@res OUTPUT 
print @res