SQL Server: User Defined Function

Creating a user defined function to identify the employee with the highest salary who meets other specific criteria. DBA/Developer use the following statement to create the UDF:

CREATE FUNCTION dba.udf_find_emp
(@Commission money, @Status varchar(8) = 'FA')
RETURNS int WITH SCHEMABINDING, ENCRYPTION AS
BEGIN DECLARE
@v_emp int;SELECT @v_emp = EmpIDFROM dba.Employee
WHERE
Status = @StatusAND Commission > @Commission AND Salary = (SELECT MAX(Salary)

FROM dba. Employee

WHERE Status = @StatusAND Commission > @Commission);
RETURN @v_emp
END;

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, DBA/Developer defined a default value for the @Status parameter. Therefore, to successfully call the function without an @Statusvalue specified, DBA/Developer must also specify the DEFAULT keyword. When calling stored procedures, DBA/Developer can omit the DEFAULT keyword, but for user-defined functions, DBA/Developer cannot. For example, in this scenario, the following statement would execute successfully:

SELECT dba.udf_find_emp(1000, DEFAULT);

When DBA/Developer creates a scalar user-defined function, the function can be used anywhere that a scalar value could be used. In this scenario, DBA/Developer 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.