TSQL - Function

Parameters

A user-defined function takes zero or more input parameters.A function can have a maximum of 1024 input parameters. When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to get the default value. This behavior is different from parameters with default values in stored procedures in which omitting the parameter also implies the default value. User-defined functions do not support output parameters.

Return

Returns either:

• a scalar value
• or a table.

Inline table-valued functions

In the below function:

• The RETURNS clause defines the format of the returned tabular variable (@OrderShipperTab).
• The returned tabular variable @OrderShipperTab is local within the function.
• The function body constructs the variable @OrderShipperTab by inserting rows into it.
• The RETURN statement cannot have an argument and return the local variable

CREATE FUNCTION LargeOrderShippers ( @FreightParm money )
RETURNS @OrderShipperTab TABLE
   (
    ShipperID     int,
    ShipperName   nvarchar(80),
    OrderID       int,
    ShippedDate   datetime,
    Freight       money
   )
AS
BEGIN

   -- Example of Intern variable
   declare @Var int
   set @Var = 10


   INSERT @OrderShipperTab
        SELECT S.ShipperID, S.CompanyName,
               O.OrderID, O.ShippedDate, O.Freight
        FROM Shippers AS S INNER JOIN Orders AS O
              ON S.ShipperID = O.ShipVia
        WHERE O.Freight > @FreightParm
   RETURN
END

Inline user-defined functions

Inline user-defined functions (or parameterized views) can be used to support parameters in the search conditions specified in the WHERE clause.

Inline user-defined functions follow these rules:

• The RETURNS clause contains only the keyword table. You do not have to define the format of a return variable because it is set by the format of the result set of the SELECT statement in the RETURN clause.
• There is no function_body delimited by BEGIN and END.
• The RETURN clause contains a single SELECT statement in parentheses. The result set of the SELECT statement forms the table returned by the function. The SELECT statement used in an inline function is subject to the same restrictions as SELECT statements used in views.
• The table-valued function accepts only constants or @local_variable arguments

CREATE FUNCTION MyTable_AsOf(@myDate datetime)
RETURNS TABLE 
AS
RETURN 
(
	select
	    *
	from
	    data_store.myTable 
	where
	    @myDate between DATE_VALID_FROM and isnull(DATE_VALID_END, '99991231')
	
		
)
GO