SQL Server: User Defined Scalar Function

DBA/Developer authored a statement to create the function:

CREATE FUNCTION dbo.udf_get_reorder_point(@v_prodid int)

RETURNS int
AS BEGIN
DECLARE @v_reorderpt int;
SELECT @v_reorderpt = ReorderPoint
FROM ProductDetails
WHERE ProductID = @v_prodid
IF (@v_prodid BETWEEN 100 AND 500)
RETURN
@v_reorderpt
RETURN 0
END;

In this scenario, DBA/Developer wanted to create a user-defined scalar function to return the ReorderPoint value for a specific product, but DBA/Developer wanted the function to return a zero value if the ProductID value was not between 100 and 500. A scalar function returns a single scalar value. DBA/Developer can create a user-defined function using the CREATE FUNCTION statement. The basic syntax of the CREATE FUNCTION statement when creating a scalar function is as follows:

 
CREATE FUNCTION [schema_name.]
function_name ([{@parm_name
[AS][parmtype_schema.] parm_data_type [=default] [READONLY]} [,...n]])
RETURNS
return_type [WITH function_opt [,...n]][AS]
BEGIN function_body
RETURN scalar_expression
END;

DBA/Developer can pass no parameters or multiple parameters into the function using the parameter list. DBA/Developer specifies the RETURNS clause to indicate the data type of the value that the function returns. Then, DBA/Developer includes Transact- SQL statements within the body of the function, and use the RETURN statement to return the value. In this scenario, the statement accepts a parameter that identifies a product in the ProductDetails table, and declares and sets the value of a variable to return the ReorderPoint value. The IF statement checks the ProductID value that was passed to the function. If the ProductID value is in the desired range, the function executes the first RETURN statement and returns the actual ReorderPoint value. However, if the ProductID is not in the desired range, the second RETURN statement executes and returns a zero value to the caller. After DBA/Developer creates the function, DBA/Developer could call the function from other Transact-SQL code. For example, DBA/Developer could use the following statement to return the ReorderPoint value for the product with a ProductID value of 100:

SELECT dbo.udf_get_reorder_point(100);

DBA/Developershould not use the CREATE FUNCTION statement that includes an ELSE because this statement will return the following error:
Msg 455, Level 16, State 2, Procedure udf_get_reorder_point, Line 16

The last statement included within a function must be a return statement. Even though the last RETURN statement may be the last executed, it is not considered the last statement in the function and will generate an error. DBA/Developer should not use the CREATE FUNCTION statement that omits the RETURNS clause because a RETURNS clause is required to identify the data type returned. The following statement will generate a syntax error:

CREATE FUNCTION dbo.udf_get_reorder_point(@v_prodid int)

AS BEGIN DECLARE
@v_reorderpt int;
SELECT @v_reorderpt = ReorderPoint
FROM ProductDetails
WHERE
ProductID = @v_prodidIF (@v_prodid
BETWEEN 100 AND 500)
RETURN @v_reorderpt
RETURN 0
END;

DBA/Developer should not use the CREATE FUNCTION statement that does not include a RETURN statement. Each function must contain a RETURN statement to return the value, and the statement must be the last statement in the function. The following statement will generate an error as shown:

CREATE FUNCTION dbo.udf_get_reorder_point(@v_prodid int)

RETURNS int AS BEGIN
DECLARE @v_reorderpt int;
IF (@v_prodid
BETWEEN 100 AND 500)
SELECT
@v_reorderpt = ReorderPoint
FROM ProductDetails
WHERE ProductID =
@v_prodidELSE SET @v_reorderpt = 0
END;

Msg 455, Level 16, State 2, Procedure udf_get_reorder_point, Line 7
The last statement included within a function must be a return statement.