SQL User Defined Functions

Sql User Defined Functions(UDF) was introduced with SQL Server 2000.
Sql User Defined Functions returns single value or table and it can accept max of 1024 parameters or min of zero parameters.

We can use this functions in SELECT , WHERE AND CASE clause.


Advantages of using UDF(User Defined Functions)

1. Make Sql code less complex.
2. Execution is Faster.
3. Create Less Network Traffic.
4. Can be used where we want to do perform repeated tasks on some data.

Disadvantages:

1. Stored procedures cannot be called inside a function.
2. Temporary tables cannot be used.
3. Transaction Statements cannot be used.

Types of User Defined functions in SQL :

1. Scalar Function

This returns a single value. That can be of datatype int, varchar etc

Example of Scalar Function:

create function AddNumbers(@num int, @num2 int) returns int
as
begin
 declare @result int
   set @result = @num + @num2
 return @result
end

Now lets call the function:

select AddNumbers(10,10) --> this will throw error when u call , to resolve this add schema name along with the function name i.e. dbo.AddNumbers

select dbo.AddNumbers(10,10) 



2. Inline Table User Defined Function

This type of function returns a table. Such function are used where we want to do some reapeadted tasks on a table.

Example:
First create a sample employee table.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employee](
[EmpDeptID] [int] NULL,
[EMpName] [varchar](100) NULL,
[salary] [numeric](18, 0) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Employee] ([EmpDeptID], [EMpName], [salary]) VALUES (2, N'Peter', CAST(5000 AS Numeric(18, 0)))
GO
INSERT [dbo].[Employee] ([EmpDeptID], [EMpName], [salary]) VALUES (3, N'Peter', CAST(100 AS Numeric(18, 0)))
GO
INSERT [dbo].[Employee] ([EmpDeptID], [EMpName], [salary]) VALUES (24, N'Mary', CAST(5000 AS Numeric(18, 0)))
GO
INSERT [dbo].[Employee] ([EmpDeptID], [EMpName], [salary]) VALUES (6, N'Anthony', CAST(500 AS Numeric(18, 0)))
GO
INSERT [dbo].[Employee] ([EmpDeptID], [EMpName], [salary]) VALUES (5, N'Jacob', CAST(1000 AS Numeric(18, 0)))
GO
INSERT [dbo].[Employee] ([EmpDeptID], [EMpName], [salary]) VALUES (3, N'Peter', CAST(5000 AS Numeric(18, 0)))
GO
INSERT [dbo].[Employee] ([EmpDeptID], [EMpName], [salary]) VALUES (0, N'Jacob', CAST(1500 AS Numeric(18, 0)))
GO
INSERT [dbo].[Employee] ([EmpDeptID], [EMpName], [salary]) VALUES (2, N'Peter', CAST(4500 AS Numeric(18, 0)))
GO

Create function returnTbl(@Salary int) returns table
as
return
select * from employee where salary > @salary

Call returnTbl function:
select * from dbo.returnTbl(2000)



3. Multi Statement Table valued User defined Function

This function too returns a table. But here we can perform other DML operations i.e Insert,delete,update other than using Select on the locally defined table variable.

Example:

Create function MultiFuncDemo(@flag int) returns @tbl table(empname varchar(50), salary numeric)
as 
begin
  if @flag = 0 
     insert into @tbl
     select EMpName,salary from employee
 else
     insert into @tbl
      select 'No Data found',0000
return 
end

call MultiFuncDemo Function:
select * from dbo.MultiFuncDemo(0)

Subscribe in a reader