In the last tutorial, l discussed stored procedures. Stored procedures are used for implementing the complex logic of a business while functions are commonly used for specific tasks. Do not confuse a stored procedure for a function, also remember there are system functions which are provided by the SQL Server. There are lot of system functions and I’m not interested in listing them because l can’t even list them. The main focus in this tutorial is the steps of creating user defined functions. User Defined Functions are set of TSQL statements that can accept parameters, perform an action, such as complex calculation, and return the result of the action as a value. The return value can either be single scalar value or result set 2D data. User Defined Function are also compiled, just like stored procedures and their execution plan is saved in the database. Let’s look at the properties of a User Defined Functions. There are basically three types of user defined functions. These are scalar functions, In-line functions and multi-line functions.
1. Scalar Functions- return only a single value.
CREATE FUNCTION udf_add(@a int, @b int) RETURNS INT
AS
BEGIN
DECLARE @c INT
SET @c = @a + @b
RETURN @c
END
Lets now call the function,
SELECT udf_add(1,2) – this should give 3
2. In-line UDF- They are functions which return tabular data.
NB. They do not have any function body and have only Return Statement
Do Not have BEGIN and END statement.
Have only one tsql select statement.
CREATE FUNCTION udf_empInfo(@eid int) RETURNS TABLE
RETURN(
SELECT c.firstname, c.lastname, c.emailAddress, c.phone
FROM Employee as e
JOIN Contact as c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID = @eid
)
To call this function, we do it like this.
SELECT * FROM udf_empInfo(10)
This will display tabular data about the employee with id 10.
3.Multi Line/table value User Defined Functions- They are functions that can return tabular data. It has its own function body BEGIN—END. It can have multiple TSQL statements. This function declares a table variable, populate it and return it to the caller.
CREATE FUNCTION udf_empInfo(@eid INT)
RETURNS @var TABLE(eid INT, fname VARCHAR(50), lname VARCHAR(50))
AS
BEGIN
INSERT INTO @var
SELECT e.EmployeeID, c.Firstname, C.Lastname
FROM Employee AS e
JOIN Contact AS c
ON e.ContactID = c.ContactID
WHERE e.EmployeeID = @eid
RETURN
END
To call this function, we do it like this
SELECT * FROM udf_empInfo(10)
This will also display the information about the employee with id 10. The difference between multi line and in line is that, multi line can have any tsql statement allowed in functions executed in it but in line contains only a select statement.
I want to also mention that, there are two kinds of functions, these are
Deterministic User Defined Function – Function in which particular given input results in particular output, output is always the same.
Non-Deterministic User Defined Function- Functions in which output does not directly depend on input even for a fixed input. For example, function to give the age in seconds given the persons birth date. This is a non deterministic function because the function will return different outputs with the same input. Lets see the code for this function.
CREATE FUNCTION udf_getageinseconds(@dob date) RETURNS BIGINT
AS
BEGIN
DECLARE @AgeInSeconds BIGINT
SET @AgeInSeconds = DATEDIFF(SECOND,@dob,GETDATE())
RETURN @AgeInSeconds
END
Now let’s call it,
SELECT udf_getageinseconds(‘May 4,1990’) as ‘Age in seconds’
Executing this function will give different results at different times even though the input is the same.
To conclude this tutorial, lets brush up with just some general information to keep about functions, just as l did for stored procedures.
1.They Must be called from SELECT statement(Implicit calling)
2.UDF must return something(scalar/table-valued)
3.UDF must have BEGIN..END block except in in-line UDF
4.UDF do not have any OUT parameters
5.UDF can return 2D data along with scalar data
6.UDF do not have any access to temporary tables
7.There is no robust-error handling available in UDF like TRY/CATCH and transactions
8.UDFs are used to implement specific complex business formulae
9.UDF cannot have Data Definition Language but it can do Data Manipulation Language only with table variables created inside of it. UDF Can’t do Data Manipulation Language
with permanent tables/views.
10.UDF can not call a Stored Procedure
UDF is my short cut for user defined functions.
I hope this helps someone, for questions, suggestions and feedback, please drop a comment. I’ll be discussing Views in the Next tutorial. Keep in touch, Peace.
