SQL 中UDF（用户自定义函数）使用

返回标量值:

GO

CREATE FUNCTION DBO.COMPARE(@A INT,@B INT)

RETURNS INT

AS

BEGIN

    DECLARE @RETURNVALUE INT

    IF @A>@B

        SET @RETURNVALUE=1

    ELSE IF @A=@B

        SET @RETURNVALUE=0

    ELSE

        SET @RETURNVALUE=-1

    RETURN @RETURNVALUE

END

GO

SELECT DBO.COMPARE(3,4)

返回表值:

GO

CREATE FUNCTION DBO.SearchPersonByType(@type varchar(50))

RETURNS TABLE

AS

    RETURN (SELECT * FROM person.Person p

            WHERE P.PersonType=@TYPE)

GO

SELECT * from DBO.SearchPersonByType('EM')