Function in SQL and use of SQL function at project level

Function in MS SQL Server,Use of function

Introduction:Function is used to perform the set of task or we can write multiple queries in a function and we can call n-number of times , So function gives me reusability of queries.

Why we create(make) function in MS SQL Server

Reusability:-Once we make a function we can call many times from asp.net application or any other application.

Actual Use of function at project level:

1.Function can return single value, it can’t return multiple value due to “returns” keyword.

2.Function can call another function.

3.At the project level the actual use of function is to perform the computational or calculation like to findout the no. of leave taken by the employee, sum of employee salary etc.

4.By the function we can fetch a record or search a record.

5.By the function we can’t perform DML operation(like insert,delete,update). This is the reason I wann go for Store procedure.

Note:

When we make a function it will go within DBO (DataBase Owner), became if we want to call the function we must include dbo.function name let’s see.

Create function [SearchName](@code int)

returns varchar(20)

as

   begin

       return 'Deepak'

   end

select dbo.SearchName(107) 

So, how many ways we can call function in Sql: There are following ways we can call function in MS SQL Server.

1.select dbo.SearchName(107)

2.print dbo.SearchName(107)

3.Declare @detail varchar(50)

SET @detail=dbo.SearchName(107)

print @detail

Type of function in MS SQL Server:

If you go under programmability section,then you can see function is 3-Type

1.System Function

2.Table-Valued Function

3.Scaler-Valued Function