# 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**                      |