MEDIAN sp for SQL 2000

,

I wrote this to calculate the median on a record set. All you need to pass is the table name and column name. If you would like to filter the record set, create a view that displays the correct record.

The limitation of not being able to pass arrays to Stored procedure or functions was the primary reason for creating it in this way.

ALTER Procedure MEDIAN @table varchar(255), @field varchar(255)

AS 
BEGIN
	DECLARE 	@medvarcnt int
	CREATE TABLE 	#medianlist2 (rid int IDENTITY(1,1), medianval int)
	DECLARE 	@SQLSTR Nvarchar(800)
	SET @SQLSTR =('INSERT #medianlist2 select ' + @field + ' AS medianval from ' + @table + ' order by ' + @field + ' desc')
	SET @SQLSTR = CAST (@SQLSTR AS NVARCHAR(800))
	EXECUTE sp_executesql @SQLSTR
	SET @medvarcnt = (SELECT COUNT(*) FROM #medianlist2)
	
	if @medvarcnt % 2 = 0
		BEGIN		
			SELECT sum(medianval)/2 AS MEDIAN FROM #medianlist2 where rid >=(@medvarcnt/2) and rid <=(@medvarcnt/2)+1
		END
		ELSE	
		BEGIN
			SELECT medianval AS MEDIAN FROM #medianlist2 where rid =(@medvarcnt/2)+1 
		END
	drop TABLE #medianlist2

END


EXEC MEDIAN  @table = 'master.dbo.sysdevices',@field = '[size]'

Rate

Share

Share

Rate