Calculating a Median without use of Cursors

This is a different version to AngelWPBTampa's article on how to find the median of a column, I have added how to do the samething without a cursor.
-- I have now also added a procedure version which accepts two parameters tablename and column name for which you want to find the median off (see bottom)
Cursors can prove to be very inefficient if incorrectly used so take some time to analyse Angel's solution and determine for yourself using profiling which solution would best suit your own needs.
For this example I use a table named aa_testtable containing two rows, OID (unique identifier) and TestValue both ints. The process is simple, determine the middle row i.e. rowcount/2,
If odd number of records then get the value from that middle ordered row,
otherwise (if even ) take the average from the two middle rows.

--Declare variables to hold the positions and results
Declare @v_rowcount bigint
DECLARE @v_odd int
DECLARE @v_mid int
declare @v_result1 decimal
declare @v_result2 decimal

--set the rowcount to be the total number of rows in your table
select @v_rowcount = count(OID) from aa_testtable

SET @v_odd = @v_rowcount % 2 --1 if odd number of recs, 0 if even
SET @v_mid = (@v_rowcount / 2)+1 --Number of the "middle" record

select @v_mid as MiddleRow

set rowcount @v_mid

select @v_result1 = TestValue from aa_testtable order by testvalue

if @v_odd = 0
begin
set @v_mid = @v_mid -1 --get the previous row
set rowcount @v_mid
select @v_result2 = TestValue from aa_testtable order by testvalue
set @v_result1 = (@v_result1 + @v_result2)/2
end

--the current value of @v_result1 is the median of column testvalue
select @v_result1



this is a proc version to find the median and allows two parameters of the tablename and fieldname and will return the median of this field in the table. Note no checks take place to ensure the correct data type etc.



CREATE PROC usp_CalcMedian
(
@pi_TableName VARCHAR(255),
@pi_MedianRow VARCHAR(255)
)
AS
BEGIN
Declare @v_rowcount bigint
DECLARE @v_odd int
DECLARE @v_mid int
declare @v_result1 decimal
declare @v_result2 decimal
DECLARE @v_SQL nvarchar(500)
DECLARE @ParmDefinition NVARCHAR(500)

--set the rowcount to be the total number of rows in your table
SET @v_SQL = 'select @v_rowcountOUT = count(*) from ' + @pi_TableName
SET @ParmDefinition = N'@v_rowcountOUT int OUTPUT'
exec sp_executesql @v_SQL, @ParmDefinition, @v_rowcountOUT= @v_rowcount OUTPUT

SET @v_odd = @v_rowcount % 2 --1 if odd number of recs, 0 if even
SET @v_mid = (@v_rowcount / 2)+1 --Number of the "middle" record

select @v_mid as MiddleRow

set rowcount @v_mid

SET @v_SQL = 'select @v_result1OUT = ' + @pi_MedianRow + ' from ' + @pi_TableName + ' order by ' + @pi_MedianRow
SET @ParmDefinition = N'@v_result1OUT int OUTPUT'
exec sp_executesql @v_SQL, @ParmDefinition, @v_result1OUT= @v_result1 OUTPUT

if @v_odd = 0
begin
set @v_mid = @v_mid -1 --get the previous row
set rowcount @v_mid
SET @v_SQL = 'select @v_result2OUT = ' + @pi_MedianRow + ' from ' + @pi_TableName + ' order by ' + @pi_MedianRow
SET @ParmDefinition = N'@v_result2OUT int OUTPUT'
exec sp_executesql @v_SQL, @ParmDefinition, @v_result2OUT= @v_result2 OUTPUT
set @v_result1 = (@v_result1 + @v_result2)/2
end

--the current value of @v_result1 is the median of column testvalue
select @v_result1

end