创建功能返回整数SQL Server 2008

Question

我正在尝试创建一个返回整数的函数。但是，我得到了警告

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

这是查询。提前致谢。

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

Answer 1

只需将返回类型更改为Integer：

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

除非您故意使用Median类型来尚未说明的内容。

Answer 2

，由于您正在计算某些值的中位数，因此建议您返回数值，而不是整数为max（@score） min min（@score）/2可以返回小数号码值。因此，尝试将该值保存在int变量中将截断小数零件。这可能导致错误的结果。

在下面的示例中，我使用了数字（20,2）返回值。

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

，或者如果您想返回整数使用圆形功能，则类似的功能。

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

Answer 3

您必须在RETURNS上声明数据类型。"中间"不是类型。

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

Answer 4

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