CODEWITHSUNDEEP
sqlsql-serversql-server-2008function
lucyh
lucyh

Reputation: 179

Create function return integer SQL Server 2008

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

Upvotes: 0

Views: 19096

Answers (4)

Murad
Murad

Reputation: 60

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

Upvotes: 0

carexcer
carexcer

Reputation: 1427

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

Upvotes: 0

M.Ali
M.Ali

Reputation: 69574

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

Upvotes: 0

D Stanley
D Stanley

Reputation: 152634

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.

Upvotes: 3

Related Questions