Create function return integer SQL Server 2008

Question

I was trying to create a function which returns to an integer. However, I got the warning as

"Msg 2715, Level 16, State 3, Procedure median, Line 1 
Column, parameter, or variable #0: Cannot find data type Median."

Here is the query. Thanks in advance.

CREATE FUNCTION dbo.median (@score int)
RETURNS Median
AS
BEGIN
DECLARE @MedianScore as Median;
SELECT @MedianScore=
(
 (SELECT MAX(@score) FROM
   (SELECT TOP 50 PERCENT Score FROM t ORDER BY Score) AS BottomHalf)
 +
 (SELECT MIN(@score) FROM
   (SELECT TOP 50 PERCENT Score FROM t ORDER BY Score DESC) AS TopHalf)
) / 2 ;
RETURN @MedianScore;
END;
GO

RETURNS Median & DECLARE @MedianScore as Median; would appear to be the problem. Try changing them both to a different data type. — AeroX
– AeroX, Commented Jan 20, 2014 at 16:20
The error message is very explicit. Did you actually read it? Hence the downvote. — Dan Bracuk
– Dan Bracuk, Commented Jan 20, 2014 at 16:21
What is meant by Level 16, State 3, Procedure median - is level 16 same as line 16? just curious — Caffeinated
– Caffeinated, Commented Jan 20, 2014 at 16:24

D Stanley · Accepted Answer · 2014-01-20 16:20:37Z

3

Just change the return type to integer:

CREATE FUNCTION dbo.median (@score int)
RETURNS integer
AS
BEGIN
DECLARE @MedianScore as integer;

Unless you're intentionally using the Median type for something that you haven't stated.

answered Jan 20, 2014 at 16:20

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

M.Ali · Accepted Answer · 2014-01-20 16:42:02Z

Since you are calculating Median of some values I would suggest you return a Numeric value instead of Integer as MAX(@score) + MIN(@score)/ 2 can return a decimal number value. so trying to save that value in an INT variable will truncate the Decimal part. which can lead to wrong results.

In the following example I have used NUMERIC(20,2) return value.

CREATE FUNCTION dbo.median (@score int)
RETURNS NUMERIC(20,2)
AS
BEGIN
DECLARE @MedianScore as NUMERIC(20,2);
SELECT @MedianScore=
(
 (SELECT MAX(@score) FROM
   (SELECT TOP 50 PERCENT Score FROM t ORDER BY Score) AS BottomHalf)
 +
 (SELECT MIN(@score) FROM
   (SELECT TOP 50 PERCENT Score FROM t ORDER BY Score DESC) AS TopHalf)
) / 2 ;
RETURN @MedianScore;
END;
GO

or if you do want to return an INTEGER use round function inside the function something like this..

CREATE FUNCTION dbo.median (@score int)
RETURNS INT
AS
BEGIN
DECLARE @MedianScore as INT;
SELECT @MedianScore=ROUND(
(
 (SELECT MAX(@score) FROM
   (SELECT TOP 50 PERCENT Score FROM t ORDER BY Score) AS BottomHalf)
 +
 (SELECT MIN(@score) FROM
   (SELECT TOP 50 PERCENT Score FROM t ORDER BY Score DESC) AS TopHalf)
) / 2, 0) ;
RETURN @MedianScore;
END;
GO

carexcer · Accepted Answer · 2014-01-20 16:43:40Z

0

You must declare a datatype on RETURNS. "Median" is not a type.

CREATE FUNCTION dbo.median (@score int)
RETURNS real   -- you can use also float(24), numeric(8,3), decimal(8,3)...
AS
BEGIN
DECLARE @MedianScore as real;
SELECT @MedianScore=
(
 (SELECT MAX(@score) FROM
   (SELECT TOP 50 PERCENT Score FROM t ORDER BY Score) AS BottomHalf)
 +
 (SELECT MIN(@score) FROM
   (SELECT TOP 50 PERCENT Score FROM t ORDER BY Score DESC) AS TopHalf)
) / 2 ;
RETURN @MedianScore;
END;
GO

edited Jan 20, 2014 at 16:43

answered Jan 20, 2014 at 16:21

carexcer

1,4452 gold badges18 silver badges27 bronze badges

Comments

Murad · Accepted Answer · 2022-10-22 08:06:38Z

0

    create function [dbo].[Sum]
    ( 
      @x int,
      @y int
    )   
    RETURNS int
    AS
    BEGIN
       return @x+@y
    END

answered Oct 22, 2022 at 8:06

Murad

627 bronze badges

Collectives™ on Stack Overflow

Create function return integer SQL Server 2008

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related