User Defined Function In SQL Server-2020

Sql Server By Sagar Jaybhay

In this article, we will understand User Defined Function in sql server with different types of function in sql server a) Scalar function b) Inline table-valued functions c) Multi-statement table-valued functions

User-defined Functions In SQL Server

User-defined functions also called UDF. In SQL server there are 3 different types of User-Defined functions

  1. Scalar function.
  2. Inline table-valued functions.
  3. Multi-statement table-valued functions.

You can find these functions folder in SQL Server Management studio see below image

User Defined Function In SQL Server
User Defined Function In SQL Server

Scalar Functions

  1. The scalar function is a function that may or may not have parameters but always return a single value. The return type of scalar function can be any data-type except text, image, cursor, and timestamp. The scalar function may or may not have parameters but it should return a single value.

Why this function called scalar function because it returns a single value.

Example

In-built function with parameter

select ABS(-1000) // op 1000

In-built function with 0 parameter

Select GetDate() //

General Syntax for Scalar function:

CREATE FUNCTION [schema_name.]function_name (parameter_list)
RETURNS  return-data-type AS
BEGIN
statements
RETURN value
END

How to create User-defined Scalar function in the SQL server?

Create function GetAge(@bdate nvarchar(100))
returns int
as 
begin
declare @age Int
set @age=DATEDIFF(YEAR,cast(@bdate as date),Getdate());
return @age;
end

In the above function, we calculate the age.

To call this function if you only use

Select function-name(parameter); then it will give an error to overcome this error we need to give a fully qualified name or 2 part name or at-least database owner and name append before the function name. In our case, dbo is a database owner.

select dbo.GetAge('12/01/1988'); // op- 31

Second example: you can use this in select clause as shown below

select id,first_name,last_name,dbo.getage(dateofbirth) from studentTable

You can do this in store-procedure also but you can not use stored procedure in select clause and where clause.

How to alter function in the SQL server? 

ALTER function [dbo].[GetAge](@bdate nvarchar(100))
returns int
as 
begin
declare @age Int
set @age=DATEDIFF(YEAR,cast(@bdate as date),Getdate());
return @age;
end

The alter keyword needs to use instead of create statement and whole function you need to write here.

How to delete or remove a function from the SQL server?

You need to use a drop keyword and function name to remove function in the SQL server.

Also, a fully qualified name or database administrator name needs to use along with function name if you use only function name then it will give an error.

General syntax:

Drop function function-name;

Example

drop function dbo.getage

Inline Table-valued function

What is the table-valued function?

The table-valued function is a user-defined function that returns the table as a data type. The return type is a table and it is treated as a table.

In this type of function, we return a table and in previous we learn in that we return a single value.

General Syntax of table-valued function:

create function function_name(@parameter datatype)
returns table
as
return (select * from table_name)
end

  1. In the above general syntax, you will see returns return type is table means this function will return table.
  2. In this function, the body is not enclosed in Begin and End block
  3. Whatever table is returned that table structure specifically depends upon what select statement you used.
  4. The table-valued function is treated as a table.

If you use, begin and end statements in this function create syntax you will get below error.

State 31, Procedure getEmployeeByGender, Line 7 [Batch Start Line 0]

–Incorrect syntax near ‘BEGIN’.

Example

create function getEmployeeByGender(@gender nvarchar(10))
returns table
as
return (select * from Employee where Gender=@gender);

In the above function, we pass gender as a parameter and this parameter is used in where clause in the below select statement.

To call this function we need to use below syntax.

Select * from function_name(parameter);

Ex

select * from  getEmployeeByGender('male')

InLine table Valued Function
InLine table Valued Function

In inline-table-valued function when you call this function you can use where clause in that.

select * from  getEmployeeByGender('male') where DepartmentId is null

function in SQL server
function in SQL server

Where we can use Inline Table-Valued functions?

  • Inline table-valued functions can be used to achieve the functionality of parametrized views
  • You can use a table which is returned by inline table valued function for joins or to form join with another table.

Query By Using Table-valued function with another table

select * from getEmployeeByGender('male') as e
join 
Department D on D.DepartmentID=E.DepartmentID;

Table valued function in sql server
Table valued function in sql server

How to modify the table-valued function?

You can use alter keyword instead of create a word in function creation. The rest of the script is the same.

alter function [dbo].[getEmployeeByGender](@gender nvarchar(10))
returns table
as
--begin
return (select EmpID,full_name,DepartmentID,Salary from Employee where Gender=@gender);
--end
GO

Multi-statement table-valued functions (MSTVF)

A multi-statement table-valued function is a table-valued function that returns the result of multiple statements.

It is useful because you can execute multiple queries inside that function.

In this type of function, you need to define a table variable and return that variable.

Also, you can create the structure of a table.

create function fn_MSTVF()
returns @tbl Table (names varchar(100),empid int, gender varchar(10),salary float(2) , departmentid int)
as
begin
insert into @tbl
select  full_name,empid,gender,salary,departmentid from Employee

return
end;



select * from fn_MSTVF() where gender='male'

Multi-statement table-valued functions (MSTVF)
Multi-statement table-valued functions (MSTVF)

What is the difference between the table-valued function and multi-statement table-valued function?

  • In the Inline-table-valued function, we don’t have the structure of a table in return statement whereas in a multi-statement table-valued function we have the structure of a table in returns clause.
  • The inline table-valued function doesn’t have to begin and end block while MSTVF has begin and end block.
  • An inline-table-valued function has better performance than MSTVF when you able to achieve function using inline-table-valued try to used over MSTVF.
  • It is possible to update a table using inline-table-valued but can’t be using MSTVF

update fn_MSTVF() set departmentid=1 where empid=1 // gives error can’t modify

update getEmployeeByGender('male') set departmentid=null where empid=1 //update successfully underlying table.

Functions Important Concept In SQL Server 2020

The function is either deterministic or non-deterministic at any given time in SQL.

Deterministic and Nondeterministic function:

It is a function that returns the same value or results at any time no matter how many times you run the same function with specific input and given the state of the database remains the same.

Ex. Square(), Avg(), Sum()

Note: all aggregate functions are deterministic functions.

Nondeterministic functions:

These are the function which may return different results each time they called with a specific set of input values even if the database state that they access remains the same.

GetDate(), CURRENT_TIMESTAMP()

Rand Function:

This function act as deterministic as well as non-deterministic based on the seed value.

  1. If you don’t provide any seed value to Rand function it will give you a different result every time until you call that function.
Select Rand()
  • When you provide seed value in case of rand function it will take an integer as a seed value and if we pass 1 every time it will give you the same result.
Select Rand(1);

Encryption of function definition using with Encryption Option:

As we are able to encrypt the stored procedure similar way we can encrypt the function also in the sql server.

See below image we create a function first and after that, we call that function to see is this working fine or not.

After this, we call this function with the help of an in-built stored procedure which is sp_helptext and this procedure is used for getting the function text.

--drop function dbo.fn_getEmployeeEmailByID;

Create function fn_getEmployeeEmailByID(@id int)
returns nvarchar(200)
as
begin
return (select email from Employee where EmpID=@id)
end;


select dbo.fn_getEmployeeEmailByID(10);


sp_helptext fn_getEmployeeEmailByID;

how to get function text in sql server
how to get function text in sql server

To avoid this and we have scenario we don’t want the end user to see our code then we need to encrypt this function and by using Encryption keyword we can do that see below code.

As we already create function we will alter and add encryption tag in that

Alter function fn_getEmployeeEmailByID(@id int)
returns nvarchar(200) with encryption 
as
begin
return (select email from Employee where EmpID=@id)
end;

how to create encrypted function
how to create encrypted function

After doing this when you run below command you will get an error.

sp_helptext fn_getEmployeeEmailByID;

Below is the error we got.

The text for object ‘fn_getEmployeeEmailByID’ is encrypted.

SchemaBinding Function:

Schema binding specifies that the function is bound to the database objects that it references. When you specify schema binding, the base object can not be modified or deleted in any way that affects our function definition.

To achive modification you need to modify your function first then you will able to modify a base object.

create function getnameByID(@id int)
returns nvarchar(10)
as
begin 
return (Select name from A where id=@id)
end;

See above function create a statement in this we use ‘A’ table which, suppose accidentally anyone drops that table or modify column names or delete columns on which our function depends then our function will not execute properly and it will throw an error.

So to avoid this we use Schemabinding Option.

Schema Binding Function
Schema Binding Function

Now our function depends on the table ‘A’. I will drop that table A.

drop table A;

select dbo.getnameByID(1);

After drop table and run our function we get below error.

Msg 208, Level 16, State 1, Line 10 Invalid object name ‘A’.

drop table error in function
drop table error in function

To avoid this we need schema binding, for that, we need to use this with SchemaBinding

So, if I want schema binding we need to alter the function and execute that function the syntax for this is below.

alter function getnameByID(@id int)
returns nvarchar(10) with SchemaBinding 
as
begin 
return (Select name from A where id=@id)
end;

By doing this we get an error which is below

Msg 4512, Level 16, State 3, Procedure getnameByID, Line 5 [Batch Start Line 1]

Cannot schema bind function ‘getnameByID’ because the name ‘A’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

To overcome this error we need to use table name in 2 parts like below dbo.A

alter function getnameByID(@id int)
returns nvarchar(10) with SchemaBinding 
as
begin 
return (Select name from dbo.A where id=@id)
end;

After this, our function modified successfully and if we try to delete the table we get below error.

Msg 3729, Level 16, State 1, Line 9

Cannot DROP TABLE ‘A’ because it is being referenced by object ‘getnameByID’.

drop table in sql server
drop table in sql server

Sagar Jaybhay, from Maharashtra, India, is currently a Senior Software Developer. He has continuously grown in the roles that he has held in the more than seven years he has been with this company. Sagar Jaybhay is an excellent team member and prides himself on his work contributions to his team and company as a whole.

Related posts