TSQL - Function
About
SQL Server (2000) supports three types of user-defined functions:
- Inline table-valued functions
- Multistatement table-valued functions
Articles Related
Syntax
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.
Example
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