use RealEstate
declare
@mySource nvarchar(50),
@calcField nvarchar(50),
@sqlDef nvarchar(250),
@sqlDefMed nvarchar(250),
@obs smallint,--numeric(14,4),
@MedLoc1 smallint,--numeric(14,4),
@MedLoc2 smallint,--numeric(14,4),
@Median numeric(14,4);

SELECT @mySource='dbo.land';
SELECT @calcField='Area';
SELECT @sqlDef = 'SELECT @obs = count(' + @calcField + ') from ' 
	+ @mySource + ' where ' + @calcField + ' IS NOT NULL';
EXECUTE SP_EXECUTESQL 
	@query = @sqlDef, 
	@params = N'@obs smallint output',
	@obs = @obs output;
	--SELECT @@ROWCOUNT as rowc  --an alternative to count
IF @Obs%2=0 BEGIN
	SELECT @MedLoc1 = @Obs/2;
	SELECT @MedLoc2 = @MedLoc1+1;
	SELECT @sqlDefMed = 'SELECT @Median = Sum(' + @calcField + ')/2 FROM (SELECT ' +
			@calcField + ', ROW_NUMBER() OVER(ORDER BY ' + @calcField + ' asc) as _Rank	FROM ' + 
			@MySource + ' WHERE ' + @calcField + ' is not null) as t WHERE t._Rank between ' + 
			rtrim(cast(@MedLoc1 as NCHAR)) + ' AND '+ rtrim(cast(@MedLoc2 as NCHAR));
	--SELECT @sqlDefMed;
	EXECUTE SP_EXECUTESQL
		@query = @sqlDefMed,
		@Params = N'@Median numeric (14,4) output',
		@Median = @Median output;
END ELSE BEGIN
	SELECT @MedLoc1 = floor(@Obs/2)+1
	SELECT @MedLoc2 = floor(@Obs/2)+1
	SELECT @sqlDefMed = 'SELECT @Median = ' + @calcField + ' FROM (SELECT ' +
			@calcField + ', ROW_NUMBER() OVER(ORDER BY ' + @calcField + ' asc) as _Rank	FROM ' + 
			@MySource + ' WHERE ' + @calcField + ' is not null) as t WHERE t._Rank between ' + 
			rtrim(cast(@MedLoc1 as NCHAR)) + ' AND '+ rtrim(cast(@MedLoc2 as NCHAR));
	--SELECT @sqlDefMed;
	EXECUTE SP_EXECUTESQL
		@query = @sqlDefMed,
		@Params = N'@Median numeric (14,4) output',
		@Median = @Median output;
END
SELECT @obs as obs, @MedLoc1 as medloc1, @MedLoc2 as medloc2, @Median as median

USE [RealEstate]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_getMedian] (
	@calcField nvarchar(50),
	@mySource nvarchar(50))
	
RETURNS numeric(14,4)
with execute as caller
AS
BEGIN
	-- Declare the return variable here
	DECLARE 
		@Median numeric(14,4),
		@sqlDef nvarchar(250),
		@sqlDefMed nvarchar(250),
		@obs smallint,
		@MedLoc1 smallint,
		@MedLoc2 smallint

	SELECT @mySource='dbo.land';
	SELECT @calcField='Area';
	SELECT @sqlDef = 'SELECT @obs = count(' + @calcField + ') from ' 
		+ @mySource + ' where ' + @calcField + ' IS NOT NULL';
	EXECUTE SP_EXECUTESQL 
		@query = @sqlDef, 
		@params = N'@obs smallint output',
		@obs = @obs output;
		--SELECT @@ROWCOUNT as rowc  --an alternative to count
	IF @Obs%2=0 BEGIN
		SELECT @MedLoc1 = @Obs/2;
		SELECT @MedLoc2 = @MedLoc1+1;
		SELECT @sqlDefMed = 'SELECT @Median = Sum(' + @calcField + ')/2 FROM (SELECT ' +
				@calcField + ', ROW_NUMBER() OVER(ORDER BY ' + @calcField + ' asc) as _Rank	FROM ' + 
				@MySource + ' WHERE ' + @calcField + ' is not null) as t WHERE t._Rank between ' + 
				rtrim(cast(@MedLoc1 as NCHAR)) + ' AND '+ rtrim(cast(@MedLoc2 as NCHAR));
		--SELECT @sqlDefMed;
		EXECUTE SP_EXECUTESQL
			@query = @sqlDefMed,
			@Params = N'@Median numeric (14,4) output',
			@Median = @Median output;
	END ELSE BEGIN
		SELECT @MedLoc1 = floor(@Obs/2)+1
		SELECT @MedLoc2 = floor(@Obs/2)+1
		SELECT @sqlDefMed = 'SELECT @Median = ' + @calcField + ' FROM (SELECT ' +
				@calcField + ', ROW_NUMBER() OVER(ORDER BY ' + @calcField + ' asc) as _Rank	FROM ' + 
				@MySource + ' WHERE ' + @calcField + ' is not null) as t WHERE t._Rank between ' + 
				rtrim(cast(@MedLoc1 as NCHAR)) + ' AND '+ rtrim(cast(@MedLoc2 as NCHAR));
		--SELECT @sqlDefMed;
		EXECUTE SP_EXECUTESQL
			@query = @sqlDefMed,
			@Params = N'@Median numeric (14,4) output',
			@Median = @Median output;
	END
	-- Return the result of the function
	RETURN(isnull(@Median,0))
END

GO

use realestate
select dbo.fn_getMedian('area','dbo.land')

quote:

---

Originally posted by GilaMonster

You cannot use dynamic SQL in a function.

Either put that as a stored procedure, or revisit the design. In general procedures or functions that take table or column names are signs of a poor database design or poor application design.

This actually looks like it should be a CLR user-defined aggregate.

--
Gail Shaw
SQL Server MVP

---

quote:

---

Originally posted by akiles

I'm not trying to be a smart-a$$ (I'm just VERY green), but why is this a bad approach?

---

quote:

---

The original reason for wanting to use dynamic sql is because I have to perform the same analysis, several times a week, on several different tables.

---

select avg(x) from table

fn_median("select avg(x) from table")

quote:

---

Originally posted by GilaMonster

quote:

---

Originally posted by akiles

I'm not trying to be a smart-a$$ (I'm just VERY green), but why is this a bad approach?

---

Apples, watermelons and spare tires.

It generally means that data model is badly designed and you (at development time) don't know what is going to be done with data. It also sometime just indicates laziness.

Would you write a function in C# that blindly did X to any object whatsoever that was passed in?

quote:

---

The original reason for wanting to use dynamic sql is because I have to perform the same analysis, several times a week, on several different tables.

---

So you have several tables storing the same kind of data? Or do you just not want to write the specific queries for each table?

--
Gail Shaw
SQL Server MVP

---

quote:

---

Originally posted by SwePeso

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=165297.
You can substitute the MONTH_NAME and NUMBER columns with a CASE statement of your choice.
After all, not all columns in a table can be used for median calculations; only numeric ones.

---

N 56°04'39.26"
E 12°55'05.63"

---