SQL Server (2000) supports three types of user-defined functions:
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.
Returns either:
In the below function:
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 (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:
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