User defined function in SQL Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

User defined function in SQL Server

Functions are basically a separate area where we write our code and according to the need we can call it.

SQL Server allows you to create functions, as we create functions in programming language. We can pass parameter to function and function can also return the output. These are fast as these are precompiled.

Consider the following table prod_rep as an example:-

Figure 1

Type of User defined function in SQL Server

According to its return type we categorized the user defined function into following two parts

Scalar function

In scalar function, we can return a single value from the function.

For example:-

Suppose I have to create function which will return the dept according to id.

Function syntax:-

create function dbo.select_dept(@id int)

returns varchar(100)

as

begin

declare @dept varchar(100);

select @dept=dept from prod_rep where id=@id;

return @dept

end

function calling:-

select dbo.select_dept(5) as dept;

the output of this code as follows:-

Figure 2

Table valued function

When we need to return the complete table from the function in this situation we create table valued function which returns a table as result set.

For example:-

In the following example I simply passing prod year and according to the prod year it will return the complete result set.

Syntax of function:-

create function dbo.ret_table(@prod_year int)

returns table

as

return

(

select * from prod_rep where Prod_year=@prod_year

);

Calling of table valued funtion:-

select * from dbo.ret_table(2001)

the output will be as follows:-

Figure 3

Hope you enjoyed the article