Tipos

Escalar

Sintaxe

CREATE FUNCTION function_name
[@param_name data_type] [=default] [READONLY] [,...,n]
RETURN return_data_type
AS
T-SQL_statements(s)

Aceiram múltiplos parâmetros.

Retornam um único valor.

• Instrução RETURN

Podem ser utilizados dentro de qualquer expressão T-SQL incluindo check constraint.

Exemplos

CREATE FUNCTION dbo.Revenue_Day (@Date datetime) RETURNS money
AS
BEGIN
    DECLARE @total money
    SELECT @total = sum(sali_Quantity * sali_price)
    FROM Sales_Orders s, Sales_Orders_Items si
    WHERE s.sal_number = si.sal_number and year(sal_date) = year(@Date)
        and month(sal_date) = month(@Date) and day(sal_date)= day(@Date)
    RETURN @total
END
GO
SELECT dbo.Revenue_Day(GETDATE())



CREATE FUNCTION dbo.fsMultiply (@A INT, @B INT = 3) RETURNS INT
AS
BEGIN
    RETURN @A * @B;
END;
GO
SELECT dbo.fsMultiply (3,4), dbo.fsMultiply (7, DEFAULT);
SELECT dbo.fsMultiply (3,4) * dbo.fsMultiply (7, DEFAULT);

Nota: O nome da schema (dbo) é obrigatório na invocação da UDF

Limitações

Determinísticas

• Os mesmos parâmetros de entrada produzem o mesmo valor de retorno.

• Não são permitidas funções não-determinísticas dentro das UDF.

  • newid,

  • rand(),

  • etc

Não são permitidos updates à base de dados ou invocação do comando DBCC.

Em termos de valor de retorno não permite:

• BLOB (binary large object) - text, ntext, timestamp, image data- type, etc;

• Table variables;

• Cursores.

Não permite TRY...CATCH ou RAISERROR.

Recursividade limitada a 32 níveis.

Inline Table-valued

Sintaxe

CREATE FUNCTION function_name
[@param_name data_type] [= default] [ READONLY ][,...,n]
RETURNS TABLE
AS
T-SQL_statement {RETURN SELECT statement}

Similares a vistas

Ambas são wrapers para construções SELECT;

Tem as mais valias das vistas acrescido do facto de suportar parâmetros de entrada.

Exemplo

CREATE FUNCTION dbo.AveragePricebyItems (@price money = 0.0) RETURNS Table
AS
    RETURN (SELECT Ite_Description, Ite_Price
                FROM Items
                WHERE Ite_Price > @price)
GO
SELECT * FROM dbo.AveragePricebyItems (15.00)



CREATE FUNCTION dbo.ftPriceList (@Code CHAR(10) = Null, @PriceDate DateTime)
RETURNS Table
AS
            RETURN(SELECT Code, Price.Price
                        FROM dbo.Price JOIN dbo.Product AS P
                                    ON Price.ProductID = P.ProductID
                        WHERE EffectiveDate = (SELECT MAX(EffectiveDate)
                                                FROM dbo.Price
                                                WHERE ProductID = P.ProductID
                                                            AND EffectiveDate <= @PriceDate)
                                    AND (Code = @Code OR @Code IS NULL));
GO
SELECT * FROM dbo.ftPriceList(DEFAULT, ‘20020220’);

UDF Multi-statement Table-Valued

Sintaxe

CREATE FUNCTION function_name
[ @param_name data_type] [= default] [ READONLY ][,...,n]
RETURNS @return_variable TABLE <table_type_definition>
AS
T-SQL_statement

Combina a capacidade das funções escalares (conter código complexo) com a capacidade das inline table-valued (retornar um conjunto).

Cria uma table variable, introduz-lhe tuplos e retorna-a.

• Tabela retornada pode ser utilizada num SELECT

Exemplo

CREATE FUNCTION dbo.AveragePricebyItems2 (@price money = 0.0) RETURNS @table TABLE (Description varchar(50) null, Price money null)
AS
    BEGIN
        INSERT @table SELECT Ite_Description, Ite_Price
            FROM Items WHERE Ite_Price > @price;
        RETURN;
    END;
GO
SELECT * FROM dbo.AveragePricebyItems2 (15.00);

SP	UDF
return - zero, single or multiple values	return - single value (scalar or table)
input/output param	input param
cannot use SELECT/ WHERE/ HAVING statement	can use SELECT/ WHERE/ HAVING statement
call SP – OK	call SP – NOK
exception handling - OK	exception handling - NOK
transactions – OK	transactions – NOK