Nth Highest Number in a record set...

----- Return Nth highest Number in a set of data
----- Ever wondered how to find out the second highest salary from the employees table?
----- Or how to find out the third oldest employee in the company?
----- Here is a stored procedure which accepts the table name, column name, and nth number
----- and displays the nth highest number from the given column.

CREATE PROCEDURE dbo.NthHighestRecord

@TableName sysname
, @ColumnName sysname
, @NthHighestNumber int

AS

----- Written by: Narayana Vyas Kondreddi
----- Date written: December 23rd 2000
----- Purpose: To find out the nth highest number in a column. Eg: Second highest salary from the salaries table
----- Input parameters: Table name, Column name, and the nth position
----- Tested on: SQL Server Version 7.0
----- Email: answer_me@hotmail.com
----- URL: http://vyaskn.tripod.com/code/nth.txt
----- Modified on Thursday May 3, 2007 by Vishal Sinha to clean up code

SET NOCOUNT ON

BEGIN
----- Trim spaces in @TableName
SELECT
@TableName = LTRIM(RTRIM(@TableName))
, @ColumnName = LTRIM(RTRIM(@ColumnName))

----- DECLARE variables
DECLARE
@SQL varchar(1000)

----- Check if Table exists
IF (SELECT OBJECT_ID(@TableName, 'U')) IS NULL
BEGIN
RAISERROR('Invalid Table Name', 18, 1)
RETURN -1
END

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
BEGIN
RAISERROR('Invalid Column Name', 18, 1)
RETURN -1
END

IF @NthHighestNumber <= 0
BEGIN
RAISERROR('Nth Highest Number should be greater than Zero', 18, 1)
RETURN -1
END

----- Create SQL Statement
SELECT
@SQL = '
SELECT
MAX(' + @ColumnName + ')
FROM
' + @TableName + '
WHERE
' + @ColumnName + ' NOT IN (
SELECT TOP
' + LTRIM(STR(@NthHighestNumber - 1)) + ' ' + @ColumnName + '
FROM
' + @TableName + '
ORDER BY
' + @ColumnName + ' DESC )'

----- EXECUTE SQL Statement
EXEC (@SQL)
END