How can I pass column to function in sql?
There are much more efficient ways to calculate a simple or grouped median than the one shown in your question:
What is the fastest way to calculate the median?
Best approaches for grouped median
The general winner for 2012 is a method by Peter Larsson. The pattern is:
Simple Median
SELECT
Median = AVG(1.0 * SQ.YourColumn)
FROM
(
SELECT NumRows = COUNT_BIG(*)
FROM dbo.YourTable
WHERE ColumnName IS NOT NULL
) AS C
CROSS APPLY
(
SELECT YT.ColumnName
FROM dbo.YourTable AS YT
WHERE YT.ColumnName IS NOT NULL
ORDER BY YT.ColumnName ASC
OFFSET (C.NumRows - 1) / 2 ROWS
FETCH NEXT 1 + (1 - C.NumRows % 2) ROWS ONLY
) AS SQ;
Grouped Median
SELECT
SQ2.GroupingColumn,
SQ2.Median
FROM
(
SELECT
GroupingColumn,
NumRows = COUNT_BIG(*)
FROM dbo.YourTable
WHERE ColumnName IS NOT NULL
GROUP BY
GroupingColumn
) AS C
CROSS APPLY
(
SELECT
Median = AVG(1.0 * SQ1.YourColumn)
FROM
(
SELECT YT.ColumnName
FROM dbo.YourTable AS YT
WHERE
YT.GroupingColumn = C.GroupingColumn
AND YT.ColumnName IS NOT NULL
ORDER BY
YT.ColumnName ASC
OFFSET (C.NumRows - 1) / 2 ROWS
FETCH NEXT 1 + (1 - C.NumRows % 2) ROWS ONLY
) AS SQ1
) AS SQ2;
To maximize the performance of the OFFSET
method above, you may need to add a locking hint (advanced topic). Suitable indexing will also be required, of course.
Code reuse
This is hard to achieve directly with a T-SQL function, since these do not allow the execution of dynamic SQL (assuming you were thinking of passing in the column name).
There are several ways around this, including using a function to generate the dynamic SQL text itself, which can then be executed by the caller. There aren't really enough details in the question to say which approach would be most suitable for you.
If you want to calculate a Median value, there are a few ways to do this depending on what version of SQL Server you (or someone else, perhaps) are on. Dwain Camps wrote two articles for Simple Talk in which he collected several pure T-SQL options from various folks, gives examples of each and compares their performance:
Calculating the Median Value within a Partitioned Set Using T-SQL
The Performance of the T-SQL Window Functions
However, I don't think you can encapsulate any of those methods into a function for easy re-use. For that you would need to use SQLCLR in order to create a User-Defined Aggregate (UDA). I wrote an article several years ago showing how to create a UDA, using Median as the example :-)
Getting The Most Out of SQL Server 2005 UDTs and UDAs
Regarding that article, please keep in mind:
- The article was written in 2007, before SQL Server 2008 came out, which introduced an improvement (i.e. allowing for storing more than 8000 bytes) that renders the compression technique shown in the article to be somewhat unnecessary. But, it should be fairly easy to remove the
GZipStream
stuff and changeMaxByteSize
from8000
to-1
. - You don't need to code anything since the article includes, at the bottom, a SQL script that installs the UDA so you can just download, run, and use the Median aggregate :-)
See also:
- CREATE AGGREGATE
- SqlUserDefinedAggregateAttribute Class
- SQL# (a SQLCLR library, which I am the author of, but there is a Free version that includes Agg_Median)