Working with User-Defined Functions in SQL Server

User-defined functions in SQL Server will be covered in this tutorial. We’ll learn how to use user-defined functions, which come in two types: scalar-valued functions and table-valued functions.

Scalar valued function always returned a scalar value

Scalar Function

Lets go to create a scalar value function

Create function add_ten(@num int) Returns int As
BEGIN Return( @num+10)
end

Select the function

select dbo.add_ten(5);

I used the function, and it produced results that were 15, so it was effective.

Result 1

Following that, we’ll examine the table value function.

Table Valued Function

The table value function returns a table instead of a scala

In this article we will see the Azure Sql Functions (Create, Alter and Drop) Statements

Below I mentioned the format of all statements, please find the below statements

CREATE FUNCTION [database_name.]function_name (parameters)
RETURNS data_type AS
BEGIN
    SQL statements
    RETURN value
END;

ALTER FUNCTION [database_name.]function_name (parameters)
RETURNS data_type AS
BEGIN
    SQL statements
    RETURN value
END;

DROP FUNCTION [database_name.]function_name;

Lets see with the examples

Step 1 : Create the Function

Before I have create a simple employee table and insert two records

CREATE TABLE [dbo].[employee](
    [EmpID] [int] IDENTITY(1,1) NOT NULL,
    [EmpName] [nvarchar](128) NOT NULL
);

insert into employee values ('Ajith')
insert into employee values ('Aarthy')
insert into employee values ('Nagarajan')
insert into employee values ('Kala')

Now I will create a Function

Create FUNCTION Employee_details (
    @employee_id int
)
RETURNS TABLE AS
RETURN
    SELECT *
    FROM [dbo].[employee]
    WHERE EmpID = @employee_id;

Call the Function

Select * from Employee_details(2)

It will return the only one record which contain the empid number is 2

Alter the function change filter and parameter condition for empName instead of empid

Alter FUNCTION Employee_details (
    @empName varchar(20)
)
RETURNS TABLE AS
RETURN
    SELECT *
    FROM [dbo].[employee]
    WHERE EmpName = @empName;

Call Statement for the new alter function

Select * from Employee_details('Ajith');

It will return the record which record contain the empname is Ajith

Result

Here we can add 10 in all user id using the scalar function and return the value in table structure

Create function add_ten(@num int)
Returns int 
As 
BEGIN 
Return(
@num+10
)
End

call statement:

select *,dbo.add_ten(empid) as emp_add_10 from employee;

Result

Conclusion:

User-defined functions are a very powerful tool. You should use them when you’ll have a calculation you’ll repeat throughout your database. E.g. calculating the tax on different products based on predefined rules (that can change during the time), is one good candidate for the function. You put all rules there, pass parameters to the function, and as a result get the desired number. But, as with everything else, do not overuse them.

Please feel free to leave a comment or your feedback if you enjoyed what you read in the meantime :)