Functions

Functions in MS SQL server

Function is also wrapper like procedure containing SQL-Commands. Functions piece of code which runs on MS sql server. Below are few points related to functions

1. Input Parameter supported in function and output parameter not supported

2. Return keyword must in function for returning value from function

3. Function gets compile each time when executed

4. We can not use error handling in functions using Try Catch block

5. We can use only select statement in functions and not update,insert and delete

Function categorized into two Types

1. User Defined Functions

2. System defined functions 

User Defined Function again categorized in two types

1. Scalar Function

2. Table Values function

Scalar Function : It return single value from function. We can use Begin End keyword with multiple sql command /complex calculation inside Begin and End block and return calculated single value from function. While executing scalar function use dbo. before function name shown below to avoid error.

CREATE TABLE Student

(

id int primary key IDENTITY(1,1) NOT NULL,

name varchar(10) NULL,

city varchar(10) NULL

)

Insert into  Student values ('Sammer', 'Mumbai')

Insert into  Student values ('Paul', 'Nagpur')

Insert into  Student values ('Javed', 'Chennai')

Insert into  Student values ('Yogesh', 'Delhi')

create function Fun_StudentCount()

returns int

As

Begin

  Declare @count int

  select @count = count(Id) from Student

  return @count

End

select dbo.Fun_StudentCount()

create function Fun_concate(@param1 varchar(20), @param2 varchar(20))

returns varchar(100)

AS

Begin

 

 return (@param1 + ' ' + @param2)

End

select dbo.Fun_concate('Asif' , 'Sayyad')

Table Values Function : It return  table data with tabular formate multiple rows and columns. Begin and End keyword not supported, this function simply return table selection from SQL query.

create function Fun_student()

returns table

as   

   return (Select * from student)

select * from Fun_student()

System Defined Function : Function available comes bydefault with  MS SQl server provided by microsoft.  We will see few system defined functions below.

To Check System defined function go to 

Database->Programmability->Functions->System Functions

select getdate() -- return current date of MS SQL server

select count(Id) from student -- count function return count of rows

select Newid() -- return unique identifier

Conclusion : Function are using for small task or specific action but not for bulk operation, transaction, error handling, and all DML operations.