Databases Reference
In-Depth Information
only. The syntax for specifying an input parameter is the same as for a user
stored procedure. If you don't need any input parameters, then an empty pair
of parentheses must follow the function name. The return data type is
required. Place the statements used to generate the value in the BEGIN…END
block. The last statement must be a RETURN statement followed by the return
value. Let's rewrite the earlier procedure used to return a customer count as
a UDF:
CREATE FUNCTION fn_CountCust
(@SPNUM CHAR(3))
RETURNS INT AS
BEGIN
DECLARE @CUST INT
SET @CUST = (SELECT COUNT(*) FROM CUSTOMER WHERE
SPNUM=@SPNUM)
RETURN @CUST
END
Whether or not you specify the relational schema when you create a func-
tion, as with any other database object, the relational schema is part of the fully
qualified function name. With most objects, if you don't specify the schema,
the default schema (typically dbo) is assumed. However, when you call a UDF,
you must specify the schema. So, to use the function we just created, you might
run:
SELECT dbo.fn_CountCust('137')
The results would be an integer value of 4. To get an output like the one you
saw in the earlier example, you could use:
DECLARE @NUM CHAR(3)
DECLARE @CUSTCOUNT INT
SET @NUM = '137'
SET @CUSTCOUNT = (SELECT dbo.fn_CountCust(@NUM))
SELECT 'The count for salesperson' + @NUM + 'is'
+ CAST( @CUSTCOUNT AS CHAR(2))
The syntax for an in-line table-valued function is similar, except that the
return type is always TABLE. Here's the basic syntax:
CREATE FUNCTION
name
([
parameter_list
])
RETURNS TABLE
[AS]
RETURN (
select_statement
)
This time, let's create a UDF that doesn't require an input parameter. Func-
tionally, this will be equivalent to a view. We want to join the CUSTOMER and
