You are a database developer on an instance of SQL Server 2008. You are creating a user-defined function to be used by the human resources department to identify the employee with the highest salary who meets other specific criteria.
You use the following statement to create the UDF:
CREATE FUNCTION dbo.udf_find_emp(@Commission money, @Status varchar(8) =’A’)
RETURNS int
WITH SCHEMABINDING, ENCRYPTION
AS
BEGIN
DECLARE @v_emp int;
SELECT @v_emp = EmpID
FROM dbo.Employee
WHERE Status = @Status
AND Commission > @Commission
AND Salary =(SELECT MAX(Salary)
FROM dbo.Employee
WHERE Status = @Status AND Commission > @Commission);
RETURN @v_emp
END
Which statement about the function is true?
A. The function can only be called from within the body of a stored procedure.
B. The function will never return a NULL value.
C. The function encrypts all parameter values passed to the function.
D. To call the function successfully without specifying the @Status parameter value, you must include the DEFAULT keyword in the function call.
Correct Answer: D
Explanation/Reference:
In this scenario, you create a user-defined scalar function using the CREATE FUNCTION statement. The basic syntax of the CREATE FUNCTION statement for scalar functions 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;
When creating the function, you defined a default value for the @Status parameter. Therefore, to successfully call the function without an @Statusvalue specified, you must also specify the DEFAULT keyword. When calling stored procedures, you can omit the DEFAULT keyword, but for user-defined functions, you cannot. For example, in this scenario, the following statement would execute successfully:
SELECT dbo.udf_find_emp(1000, DEFAULT);
The options that state that the function can only be called from within the body of a stored procedure, and that the function encrypts all parameter values passed to the function, are both incorrect. There are no options to specify when you are creating a function to restrict where a function maybe called from or to encrypt parameter values. When you create a scalar user-defined function, the function can be used anywhere that a scalar value could be used. In this scenario, you specified WITH SCHEMABINDING, ENCRYPTION. The SCHEMABINDING option is used to ensure that no objects on which the function depends are modified in a way that might make the function unusable. The ENCRYPTION option will encrypt the function’s definition to ensure that it is not stored in plain text that is readable by others.
The option that states the function will never return a NULL value is incorrect. Even though the return type is defined as an int, the function may still return a NULL value if the SELECT statement returns no rows.
