User Defined Function

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:-

 

Fig-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.

Script:

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

To Call Function:-

Select dbo.select_dept(5) as dept;

The output of this code as follows:-

Fig-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.

Script:

Create function dbo.ret_table (@prod_year int)

returns table

as

return

(

Select  * from prod_rep where prod_yet=@prod_year

);

To call Table Valued Function:-

Select * from dbo.ret_table(2001) ;

The output is: