Function that return the median

Question

I want to create a function that returns the median of a field. I'm working with sql 2000. I wrote:

create function mediana 
(@tabla, @campo)
returns int
as
begin

declare @Median integer
return  @Median = 

(
   (SELECT MAX(@campo) FROM
     (SELECT TOP 50 PERCENT dia
      FROM @tabla
      ORDER BY @campo) AS t
    )
 + (SELECT MIN(@campo) FROM
     (SELECT TOP 50 PERCENT  @campo
      FROM @tabla ORDER BY @campo DESC) AS b
    )

) / 2.0 ;

But I get many errors. How can I solve that, also I would like to apply this function to data grouped by other fiels. Would this be possible with this code?

ps: I've never created a function in sql before.

thanks

Well where did you find syntax like `return @Median =`? Did you make that up? I mean, there *is* [documentation to consult](http://technet.microsoft.com/en-us/library/aa258261(v=sql.80).aspx). — Aaron Bertrand, Sep 22 '14 at 19:10
You need to use dynamic SQL if you are passing in a table name. I don't think SQL Server 2000 supports table valued parameters, and syntax isn't right for that anyway. — Gordon Linoff, Sep 22 '14 at 19:10
@Gordon perhaps, I have never seen it ([and can't get `return @var = anything` to work](http://sqlfiddle.com/#!3/68b32/548)), still the input parameters don't have types, no schema on the function, no `END`... basically a "find all the things wrong in this picture" exercise. — Aaron Bertrand, Sep 22 '14 at 19:12
@AaronBertrand . . . I didn't mean "syntax", I meant "logic". — Gordon Linoff, Sep 22 '14 at 19:17
@AaronBertrand ok I know it's wrong. that's why I'm asking. Would you please correct me? — GabyLP, Sep 22 '14 at 19:18
Thanks @Gordon, I think I'm familiar with the logic (see [here](http://sqlperformance.com/2012/08/t-sql-queries/median) and [here](http://sqlperformance.com/2014/02/t-sql-queries/grouped-median)) - the OP is complaining about syntax errors. — Aaron Bertrand, Sep 22 '14 at 19:19
You need to write a function that compiles; please [consult the docs I've already pointed you to](http://technet.microsoft.com/en-us/library/aa258261(v=sql.80).aspx) and give up on the idea that you can pass a table name to a function. — Aaron Bertrand, Sep 22 '14 at 19:20
really? @AaronBertrand, you are a genious! yes I download it from there! and now, can someone please help me to do what I'm asking: I want to create a function and store it to apply it to other variables. Is it possible to do so? — GabyLP, Sep 22 '14 at 19:21
Why divide by 2.0 when returning int? do you need decimal or not? — Horaciux, Sep 22 '14 at 19:25
@AaronBertrand did we have QUOTENAME() function in Sql Server 2000 ? I am not sure, because this can make my suggested solution much safer, could you please confirm if we had that function in SQL Server 2000 or not ? — M.Ali, Sep 22 '14 at 19:28
@M.Ali [Yes, you can check the documentation, I am not a library](http://technet.microsoft.com/en-us/library/aa238369(v=sql.80).aspx). :-) — Aaron Bertrand, Sep 22 '14 at 19:29
@M.Ali I searched [`quotename sql server 2000`](https://www.google.com/search?q=quotename+sql+server+2000) - first hit. Not sure how you would have searched differently for that. — Aaron Bertrand, Sep 22 '14 at 19:32
Here is some light reading for you. http://sqlmag.com/sql-server-2012/sql-server-2012-solutions-median-calculation — Dan Bracuk, Sep 22 '14 at 19:33
My Search returned this [`Page`](http://msdn.microsoft.com/en-GB/library/ms176114.aspx) and it doesnt show option for 2000 version :S my bad — M.Ali, Sep 22 '14 at 19:34
@AaronBertrand, is your answer to this question any good? http://stackoverflow.com/questions/7310335/median-in-sql-server-2000 — Dan Bracuk, Sep 22 '14 at 19:41
@Dan not really, the OP is having fundamental problems with FUNCTION syntax and the logic of passing table and column names dynamically, not any difficulty with calculating median per se. — Aaron Bertrand, Sep 22 '14 at 19:44

M.Ali · Answer 1 · 2014-09-22T19:45:55.103

When passing Table names of Column names as variables, you will end up using Dynamic sql, and you cannot use Dynamic sql inside a User defined Function this leaves you with the only option of stored procedure, Since you are returning a value back, OUTPUT parameter is your friend something like this .....

ALTER PROCEDURE mediana 
 @tabla nvarchar(128)
,@campo nvarchar(128)
,@Median DECIMAL(10,2) OUTPUT
AS
BEGIN
   SET NOCOUNT ON;
DECLARE @Sql NVARCHAR(2000);

SET @Sql = N'SET @Median =  ( SELECT 
                (
                 (SELECT MAX('+ QUOTENAME(@campo) +') FROM
                   (SELECT TOP 50 PERCENT '+ QUOTENAME(@campo) +' FROM '+ QUOTENAME(@tabla) 
                 + N' ORDER BY '+ QUOTENAME(@campo) +') AS BottomHalf)
                 +
                 (SELECT MIN('+ QUOTENAME(@campo) +') FROM
                   (SELECT TOP 50 PERCENT '+ QUOTENAME(@campo) +' FROM '+ QUOTENAME(@tabla) 
                 + N' ORDER BY '+ QUOTENAME(@campo) +' DESC) AS TopHalf)
                ) / 2  )'

EXECUTE sp_executesql @Sql
                     ,N'@Median DECIMAL(10,2) OUTPUT'
                     ,@Median OUTPUT

END

Thanks. How can I use it? Would be something like mediana(@tabla=tbl1, @campo=field1)??? — GabyLP, Sep 22 '14 at 19:29
You're not going to be able to use this in select statements, though — LittleBobbyTables - Au Revoir, Sep 22 '14 at 19:30

Function that return the median

1 Answers1