Thursday, November 10, 2011

SQL Triggers - An Introduction

Introduction
Triggers can be defined as the database objects which perform some action for automatic execution whenever users try to do execute data modification commands (INSERT, DELETE and UPDATE) on the specified tables. Triggers are bound to specific tables. As per MSDN, triggers can be defined as the special kind of stored procedures. Before describing the types of triggers, we should first understand the Magic tables which are referenced in triggers and used for reuse.


Magic Tables
    There are two tables Inserted and deleted in the SQL Server, which are popularly known as the Magic tables. These are not the physical tables but the SQL Server internal tables usually used with the triggers to retrieve the inserted, deleted or updated rows. These tables contain the information about inserted rows, deleted rows and the updated rows. This information can be summarized as follows:


Action              Inserted                 Deleted
Insert Table contains all the inserted rows Table contains no row
Delete Table contains no rows Table contains all the deleted rows
Update Table contains rows after update Table contains all the rows before update


Difference between Stored Procedure and Trigger


1) We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete and update) is fired on the table on which the trigger is defined.


2) We can call a stored procedure from inside another stored procedure but we can't directly call another trigger within a trigger. We can only achieve nesting of triggers in which action (insert, delete and update) defined within a trigger can initiate the execution of another trigger defined on the same table or different table. 


3) Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.


4) Stored procedure can take the input parameters, but we can't pass the parameters as an input to a trigger.


5) Stored procedures can return values but a trigger cannot return a value.


6) We can use the Print commands inside the stored procedure to debug purpose but we can't use the print command inside a trigger.


7) We can use the transaction statements like begin transaction, commit transaction and rollback inside a stored procedure but we can't use the transaction statements inside a trigger.


8) We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but we can't call a trigger from these files.


DML Triggers
       
Types of trigger
   In SQL Server, there are two types of triggers which are given below:-
  1.    After Triggers
  2.    Instead of Triggers
In this article, we will use three tables named customer, customerTransaction and Custmail whose structure is given below:-


Create table customer (customerid int identity (1, 1) primary key,Custnumber nvarchar(100), custFname nvarchar(100), CustEnamn nvarchar(100), email nvarchar(100), Amount int, regdate datetime)


Create table customerTransaction(Transactionid int identity(1,1)primary key,custid int,Transactionamt int, mode nvarchar, trandate datetime)


Create table Custmail (Custmailid int identity (1, 1) primary key, custid int, Amt int, Mailreason nvarchar(1000))


 After Triggers:-
   "After Triggers" are executed after the data modification action (Insert, Delete, Update) is executed on the respective tables. A table can have multiple triggers defined on it.


Syntax of the After trigger
Create Trigger trigger_name
On Table name
For Insert/Delete/update
As
 Begin


    //SQL Statements
End


Example of After Trigger for Insert


Suppose we have a requirement that whenever a new customer is added then automatically its corresponding value must be inserted into the table Custmail so that an email can be send to the customer and an authorized person in the Bank. To solve this problem we can create a After Insert trigger on the table customer whose syntax is given below:-


Create Trigger trig_custadd on Customer
For Insert
As
Begin
  Declare @Custnumber as nvarchar(100)
  Declare @amount as int
  Declare @custid as int


  Select @Custnumber=Custnumber, @amount=Amount
  From inserted


  Select @custid=customerid
  From customer
  Where Custnumber =@Custnumber


  Insert Into Custmail (custid,Amt,Mailreason)
        Values (@custid,@amount,'New Customer')
End


This trigger will be fired, whenever a new Customer is added to the bank and the corresponding entry is inserted into the table Custmail. The mail functionality will use the entries from the table custmail to send the mail to the Customer.


Example of After Trigger for Delete


Suppose, there is an another requirement that whenever a customer is deleted from the system, a mail is send to the customer containing the notification about deletion.To sends the mail, we need to insert an entry of the customer in the table custmail, whenever a customer is deleted from the master table customer. To achieve this we will use the after trigger for deletion. In the example given below, we will use the magic table Deleted.

Create trigger trig_custdelete
on customer
for delete
as
begin
 Declare @Custnumber as nvarchar(100)
 Declare @custid as int
 select @Custnumber=Custnumber from deleted
 select @custid=customerid from customer where Custnumber =@Custnumber
 delete from customerTransaction where custid=@custid
   insert into Custmail 
   values(@custid,0,'Customer delete')

end

Example of After Trigger for Update

Suppose, we have also a requirement that whenever a client credit his account or updated his name (first name as well as last name), a mail should be send to the customer containing this information. In this case, we can use the After trigger for update. In this example,we are going to use the Magic table Inserted.

create trigger trig_Custupdate
on customer
for update
as
begin
  declare @Custnumber as nvarchar(100)
  declare @amount as int
  Declare @custid as int
  if update(amount)
    begin
        select @Custnumber=Custnumber, @amount=Amount from inserted
        select @custid=customerid from customer where Custnumber =@Custnumber
        insert into Custmail 
        values(@custid,@amount,'Customer Amount Update')
    end
  if update(custFname)or update(CustEnamn)
    begin
   insert into Custmail 
   values(@custid,0,'Customer Name Update')
    end
end


In the above example, we used the Update function on the columns amount, custfname and custEname which initiates the update trigger on modification of these columns.


Instead of Triggers
    Instead of trigger is used when we want to perform another action instead of the action which causes the trigger to fire. Instead of trigger can be defined in case of Insert, Delete and Update. For example, suppose we have a condition that in a single transaction a user could not be able to debit more than $15000. We can use the Instead of trigger, to implement this constraint. If the user try to debit more than $15000 from his account at a time then error is raised with the message "Cannot Withdraw more than 15000 at a time". In this example we use the magic table Inserted.


Create trigger trigg_insteadofdelete
on customerTransaction
instead of insert
as
begin
      declare @Custnumber as nvarchar(100)
      declare @amount as int
      Declare @custid as int
      Declare @mode as nvarchar(10)
      select @custid=custid , @amount=Transactionamt,@mode=mode from
      inserted
      if @mode='c'
         begin
           update customer set amount=amount+@amount where 
           customerid=@custid
           insert into Custmail 
           values(@custid,@amount,'Customer Amount Update')
         end
      if @mode='d'
          begin
             if @amount<=15000
        begin
  update customer set amount=amount-@amount where 
                  customerid=@custid
  insert into Custmail 
  values(@custid,@amount,'Customer Amount Update')
        end
             else
begin
  Raiserror ('Cannot Withdraw more than 15000 at a time',16,1)
  rollback;
       end
          end
end


DDL Triggers



 DDL Triggers has the similar behavior as the DML triggers to have except that they are fired in response to a DDL type event like Alter command, Drop command and Create commands. In other words, it will fire in response to the events which try to change the schema of the database. Therefore, these triggers are not created for a particular table, but they are applicable to all the tables on the database. Also DDL triggers can be fired only after the commands which make them fire is executed. They can be used for the following purposes:


1) To prevent any changes to the database Schema
2) If we want to store the records of all the events, which change the database schema.


For example, suppose we want to create a table command_log which will store all the user commands for creating tables (Create table) and commands which alter the tables. Also we don't want any table to be dropped. Therefore if any drop table command is fired, a DDL trigger will rollback the command with a message that "You can't drop a table".


The script for the table  command_log will be given below:



CREATE TABLE Command_log(id INT identity(1,1), Commandtext NVARCHAR(1000), Commandpurpose nvarchar(50))

DDL Trigger for Create_table

For storing the create table command in the table command_log , we first need to create a trigger which will be fired in response to the execution of the Create table command. 



CREATE TRIGGER DDL_Createtable
ON database 
FOR CREATE_Table
AS 
  Begin
     PRINT 'Table has been successfully created.'
     insert into command_log ()
     Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText ) [1] ','nvarchar(1000)')


  End


This trigger will be fired whenever any command for the table creation is fired and will insert the command into the table command_log and also print the message that "Table has been successfully created".


Note:  Eventdata() is a functions which returns information about the server or database events.It returns value of XML type. Read more about Eventdata()

DDL Trigger for Alter_Table


Suppose if we want to store the alter table commands also in the table command_log, we need to make a trigger for Alter_table command.


Create Trigger DDL_Altertable
On Database
for Alter_table
 as
   begin
declare @coomand as nvarchar(max)
       print 'Table has been altered successfully'
       insert into command_log(commandtext)
       Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]','nvarchar(1000)')
       
  end


This trigger will be fired whenever any alter table command is fired on the database and will print the message "Table has been altered successfully."


DDL Trigger for Drop_Table


To stop the user from dropping any table in the database, we need to create a trigger for drop table command.


Create TRIGGER DDL_DropTable
ON database 
FOR Drop_table
AS 
 Begin
     PRINT 'Table cannot be dropped.'
     INSERT into command_log(commandtext)
     Select EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/ CommandText)[1]','nvarchar(1000)')
     Rollback;
end


This trigger will not allow any table to be dropped and also print the message the "Table cannot be dropped."


Nested Triggers

Nested Trigger: - In Sql Server, triggers are said to be nested when the action of one trigger initiates another trigger that may be on the same table or on the different table. 
For example, suppose there is a trigger t1 defined on the table tbl1 and there is another trigger t2 defined on the table tbl2, if the action of the trigger t1 initiates the trigger t2 then both the triggers are said to be nested. In SQL Server, triggers can be nested up to 32 levels. If the action of nested triggers results in an infinite loop, then after the 32 level, the trigger terminates.
 Since the triggers are executed within a transaction, therefore failure at any level of within nested triggers can cancel the entire transaction, and it result in total rollback.


We can also stop the execution of nested triggers through the following SQL Command:


sp_CONFIGURE 'nested_triggers',0
GO

RECONFIGURE
GO


Recursive triggers
  In SQL Server, we can have the recursive triggers where the action of a trigger can initiate itself again. In SQL Server, we have two types of recursion.
  1.   Direct recursion
  2.   Indirect recursion
  In Direct recursion, action of a trigger initiates the trigger itself again which results in trigger calling itself recursively.
  In Indirect recursion, action on a trigger initiates another trigger and the execution of that trigger again calls the original trigger, and this happen recursively. Both the triggers can be on the same table or created on the different tables.


Please note: Recursive trigger can only be possible when the recursive trigger option is set.


Recursive trigger option can be set using the following SQL Command:


ALTER DATABASE databasename 
SET RECURSIVE_TRIGGERS ON | OFF


How to find the Triggers in a database


1)  Finding all the triggers defined on whole the database
Suppose we want to get the list of all the triggers and their respective tables name then we can use the following SQL Statement.


 select o1.name, o2.name from sys.objects o1 inner join sys.objects o2 on  o1.parent_object_id=o2.object_id and o1.type_desc='sql_trigger'


2) Finding all the triggers defined on a particular table


 For example if we want to find out all the triggers created on the table Customer then we can use the following SQL Statement:-


sp_helptrigger Tablename
example:-
sp_helptrigger 'Customer'


3)  Finding the definition of a trigger


    Suppose if we want to find out the definition of the trigger, we can use the following SQL Statement:-


   sp_helptext triggername
For example:-
  sp_helptext 'trig_custadd'


Result






How to Disable a trigger


DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL }
ON { object_name | DATABASE | ALL SERVER } [ ; ]

Disabling a DML trigger on a table

DISABLE TRIGGER 'trig_custadd' ON Customer;
Disabling a DDL trigger
 DISABLE TRIGGER 'DDL_Createtable' ON DATABASE;
 Disabling all triggers that were defined with the same scope
 DISABLE Trigger ALL ON ALL SERVER;
How to enable a trigger


 Enabling a DML trigger on a table
ENABLE Trigger 'trig_custadd'  ON Customer;

Enabling a DDL trigger

ENABLE TRIGGER 'DDL_Createtable' ON DATABASE;

Enabling all triggers that were defined with the same scope

ENABLE Trigger ALL ON ALL SERVER;

How to drop a trigger.

Dropping a DML trigger

DROP TRIGGER trig_custadd ;

Dropping a DDL trigger

DROP TRIGGER DDL_Createtable ON DATABASE;

Real life example


Some weeks ago one of my friends gets a task which needs to be completed on a very old written code. The task includes that a mail should be sent to the user in the following cases:

1. The user is added to the system.

2. Whenever any information regarding the user is updated or deleted or added.

3. A user is deleted.

The challenges in this task include:-

1. The code is very old and unstructured. Therefore, it has many inline queries written on the various .aspx pages. 

2. Queries for the insert, delete and update is also written in many stored procedures.

So the code doesn't have a common library function or a standard stored procedure which is used throughout the application which can be used to insert, update and delete a user, which is not a good practice. But it happen sometimes with the old code. The required queries are written on many .aspx pages and stored procedures.

Possible solutions:


  To complete this task, we need to insert an entry into the table tblmail with proper flags indicating the insert, delete and update. A scheduled application built in .net application will read the rows from the table tblmail and send the mails. 


Two approaches to insert the rows:


1. Find all the places in the .aspx files and the stored procedures where the queries for the insert, delete and update and after these queries, add the insert query for the table tblmail.

2. Instead of finding these queries in all the .axps files and stored procedures, create after (insert, update and delete) trigger on the user master table will insert the date in the table tblmail after the execution of the insert, update and delete statement.

We used the second approach because of the following 4 reasons:

1) It is very difficult to search so many .aspx files and stored procedures to find the required queries.

2) It has the risk that a new developer may not know about this requirement of sending mail and forget to add the code for inserting the values in the table tblmail.

3) If we need to change anything in the requirement, it has to be changed in all these files and stored procedures.

4) With the second approach, we only need to create triggers on the table and the developer, and it will also minimize the risk mention in the three 3 points mention above. 

Advantages of SQL Triggers
 1) It helps in maintaining the integrity constraints in the database tables, especially when the primary key and foreign key constrain are not defined.


 2) It sometimes also helps in keeping the SQL codes short and simple as I show in the real-life example.


3) It helps in maintaining the track of all the changes (update, deletion and insertion) occurs in the tables through inserting the changes values in the audits tables.


4) Sometimes if the code is not well managed, then it can help in maintaining the database constraints defined on the tables on which the trigger is defined. For example, suppose if have a situation that there is an online learning system in which a user can register in the multiple course.


 Suppose the organization wants to define a constraint is defined that a user cannot be deleted until he/she passed all the course in which he is registered or the user has to first himself from all the incomplete or failed courses.


 Since the code is not well managed and the code to delete the user is defined as the inline query in many .net pages and multiple stored procedures (this is not a good thing, but it happens), one has to write the code for enforcing this constraint in to all these .net files and stored procedures, which take so much time and also if the new developer does not this constraint and forgets to include the constrain enforcing code which corrupt the database. In this case, we can defines an instead of trigger on the table which checks every time a user is deleted and if the condition of the above constraint is not met, display the error message instead of  deleting user.


Disadvantages of Triggers


1) Hard to maintain since this may be a possibility that the new developer doesn't able to know about the trigger defined in the database and wonder how data is inserted, deleted or updated automatically.

2) They are hard to debug since they are difficult to view as compared to stored procedures, views, functions, etc.

3) Excessive or over use of triggers can slow down the performance of the application since if we defined the triggers in many tables then they kept automatically executing every time data is inserted, deleted or updated in the tables (based on the trigger's definition) and it makes the processing very slow.

4) If complex code is written in the triggers, then it will slow down the performance of the applications.

5) The cost of creation of triggers can be more on the tables on which frequency of DML (insert, delete and update)  operation like bulk insert is high.

Conclusion
  Trigger is bad or good depends upon its use and its proper documentation. It can be very useful when it is used to maintain the integrity constraints in the database tables in the absence of primary key and foreign key, or it is very useful for the auditing purpose in tracking all the changes. But, if it is used extensively, it can reduce the performance. Also to maintain it and making debugging simple, proper documentation of the Triggers is necessary, which records the trigger name, table name on which it is created, its definition and its purpose.    


    
DMCA.com

Tuesday, August 2, 2011

SQL Server Functions- An Introduction

SQL FUNCTION: -
   Function in a database can be defined as the code segment consisting of a logical group of SQL statements which takes some input arguments (if required), run in a sequential order and returns the output either in the form of a single value or in the form of a table.Through this article, I am trying to give the overview of the System defined functions,User defined functions, their advantages, their disadvantages and their differences with the Stored procedures. 


Types of Functions: -


In SQL, Functions can be categorized into two categories:-
  1. System Defined Function
  2. User Defined Function (UDF)
In this article, I am going to use the following tables for the examples:-

CREATE TABLE [dbo]. [Employee](
[Empid] [Int] IDENTITY (1, 1) NOT NULL Primary key,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLAStName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Managerid] [int] NULL
)

CREATE TABLE [dbo].[Department](
[Departmentid] [int] IDENTITY (1, 1) NOT NULL primary key,
[DepartmentName] [nvarchar](255) NOT NULL
)

CREATE TABLE [dbo].[EmpdepartmentInfo]
(
 Empdepartmentid int identity(1,1) primary key,
 Empid int not null,
 departmentid int not null
)

SQL scripts for entering the data into the table Employee:-

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Values('A001','Samir','Singh','samir@abc.com',2)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Values('A002','Amit','Kumar','amit@abc.com',1)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Values('A003','Neha','Sharma','neha@abc.com',1)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Values('A004','Vivek','Kumar','vivek@abc.com',1)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Values('A005',' AvinASh', 'Dubey','avinASh@abc.com',2)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Values('A006',' Sanjay','Kumar',' sanjay@abc.com',5)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Values('A007','Rajiv','Kumar','rajiv@abc.com',5)

Insert Into Employee (EmpNumber,EmpFirstName,EmpLAStName,EmpEmail,Managerid)
Values('A008','Manish','Kumar','manish@abc.com',6)

SQL scripts for entering the data into the table Department:-

Insert Into Department(DepartmentName)
Values('Testing')

Insert Into Department(DepartmentName)
Values('Admin')

Insert Into Department(DepartmentName)
Values('HR')

Insert Into Department(DepartmentName)
Values('Technology')

SQL scripts for entering the data into the table EmpdepartmentInfo:-

Insert Into [EmpdepartmentInfo](empid, departmentid)
Values(1,1)

Insert Into [EmpdepartmentInfo](empid, departmentid)
Values(2,2)

Insert Into [EmpdepartmentInfo](empid, departmentid)
Values(3,3)

Insert Into [EmpdepartmentInfo](empid, departmentid)
Values(4,4)

Insert Into [EmpdepartmentInfo](empid, departmentid)
 Values(4,5)

Insert Into [EmpdepartmentInfo](empid, departmentid)
Values(5,1)

Insert Into [EmpdepartmentInfo](empid, departmentid)
Values(6,2)

Insert Into [EmpdepartmentInfo](empid, departmentid)
Values(7,3)

Insert Into [EmpdepartmentInfo](empid, departmentid)
Values(8,4)

System defined function can again be further divided into further subsections which are given below:-


1. Aggregate function.
 Example: - Avg (), Min (), Max ()


2. Configuration function.
 Example:-@@servername (), @@version()


3. Cursor function.
Example: -@@Fetch_status


4. Date and Time function.
Example: - Getdate (), Month (), Day (), Year ()


5. Mathematical function.
 Example: - Floor (), Abs ()


6. Metadata function.
 Example: - Db_Name (), File_Name ()


7. Other functions.
 Example: - cast (), convert ()


8. Rowset function.
Example: - Openxml (), Openrowset ()


9. Security function.
Example:-user(), User_Id (), User_Name ()


10. String function.
Example: - Char (), Left (), Len ()


11. System Statistical function.
 Example:-@@connections


12. Text and Image function
Example: - Textvalid ()


Types of UDF:-


Similarly, UDF can be divided Into 3 categories:-
  1. Scalar UDF
  2. Inline Table UDF
  3. Multi statements UDF
Scalar UDF:-
The UDFs which only returns only single values comes into this category.


Syntax for creating Scalar UDFs:-


CREATE FUNCTION (FUNCTION name)
(
 (Input Variable name) (data type)
)
Returns (returning variable data type)
AS
 BEGIN
 (FUNCTION body)
 Return (returning variable name)
 End


Example


CREATE FUNCTION fn_getempname
(
 @empid Int
)
RETURNS nvarchar(100)
AS
BEGIN
DECLARE @empname AS Nvarchar(100)


SELECT @empname=empfirstname + ' ' + emplAStname
FROM  employee
WHERE empid=@empid


RETURN @empname
END


Syntax for executing the Scalar UDF


SELECT dbo. (FUNCTION name) (input variable name)


For example, to execute the above UDF we use the following syntax:-


SELECT DBO.[fn_getempname](1) /* Here 1 is the empid*/


or


SELECT DBO.[fn_getempname](1) AS EmployeeName/*To get the output under the column EmployeeName */


If we want to store the value of this FUNCTION in an variable the we have to use the following syntax:-


DECLARE @name AS nvarchar(100)
SELECT @name =dbo.[fn_getempname](1)
SELECT @name


Here we first needs to define a variable (@name) which will be going to store the value return by the function and then used this variable in the SELECT statement to store the value.


Result
EmployeeName
Samir Singh


Explanation


This function will return the name of the employee whose empid we passed in the function as the Input parameter.


Inline Table UDF:-


The UDF which contains a single inline SQL statement and returns the data in the form of table is called Inline Table UDF.


Syntax for creating Inline Table UDF:-


CREATE FUNCTION (FUNCTION name)
(
 (Input Variable name) (data type)
)
Returns Table
AS
Return (SELECT statement)


Example:-

Create Function fn_getempinfo
(
 @empid Int
)
Returns Table
As
 Return Select empid,empnumber,empfirstname,emplastname,empemail 
           From employee 
           Where empid=@empid


Syntax for executing the Inline Table UDFs


Select (columns names) from dbo. (Function name) (Input Parameter)



SELECT empid,empnumber,empfirstname,emplastname,empemail
FROM dbo.[fn_getempinfo](1)


Result


Empid  Empnumber  Empfirstname Emplastname   Empemail


1         A001               Samir              Singh               samir@abc.com


Explanation


This FUNCTION will return the columns empid, empnumber, empfirstname, emplAStname, empemail of the employee AS a table variable whose employeeid is given AS the input parameter to the FUNCTION.


Multi statements UDF: -


The UDFs which contain multiple SQL statements to returns the data in the form of table is called Multi Statements UDFs.


Syntax for creating Multi Statements UDFs: -


Create Function
(
 (Input Variable name) (data type)
)
Returns (table variable) Table (table columns)
As
 Begin
 (Function body)
 Return
 End

Example


CREATE FUNCTION fn_GetEmpdepartmentinfo
(
 @empid Int
)
 Returns @Empproject Table
 (
 Employeename Nvarchar(100),Empemail Nvarchar(50),Departmentname Nvarchar(100)
 )
AS
BEGIN


 Insert Into @Empproject(Employeename,Empemail,Departmentname)
 SELECT empfirstname + ' '+ emplAStname ,empemail,departmentname 
  FROM employee Inner Join EmpdepartmentInfo On employee.empid=EmpdepartmentInfo.empid 
  Inner join Department On EmpdepartmentInfo.departmentid=Department.departmentid
 WHERE employee.empid=@empid


RETURN
END


Syntax for executing the Multi Statements UDF


Select (columns names) from dbo. (Function name) (Input Parameter)


SELECT Employeename,Empemail,Departmentname 
FROM dbo.[fn_GetEmpdepartmentinfo](1)


Result


Employeename     Empemail              Departmentname 
Samir Singh          samir@abc.com        Accounts


Explanation


This function will returns the Employeename,Empemail,Departmentname of the employee whose empid we pass as the input parameter to the function.



Difference between UDFs and Stored Procedures:-
  1. A stored procedure can return a value or it may not return any value but in case of function, a function has to return a value.
  2. Stored procedure in SQL Server cannot we executed within the DML statement. It has to be executed with the help of EXEC or EXECUTE keyword but a function can be executed within the DML statement.
  3. A function can be called from within the Stored Procedure but a stored procedure cannot be called from within a function.
  4. We can use result set return by the function as a table in Join statements but we can't use ResultSet return from stored procedure as table in Join statements.
  5. Transaction management is not possible in function but it is possible in Stored procedures.
  6. Print function cannot be called within the function but it can be called within the stored procedure.
Advantages of UDF:-

1. SQL Functions can be used in a DML statement. It means we can use execute the FUNCTION within the SELECT statement.


2. We can use the recursive FUNCTION to get the hierarchical information.


For example, if we want to get the all the employee which are directly or indirectly have the manager whose empid is given AS the input parameter(@managerid), then we can use the following FUNCTION which calls itself.


CREATE FUNCTION fn_recuursivesample
(
 @managerid int,
 @mode int
)
Returns @temporder table(employeeid int , managerid int)
AS


BEGIN


 DECLARE @count AS int
 DECLARE @empid AS int
 DECLARE @next_empid AS int
 DECLARE @next_orderid AS int


 IF @mode=0
 BEGIN
    INSERT Into @temporder
    SELECT @managerid,(SELECT managerid FROM employee WHERE empid=@managerid)
END


SELECT @count=count(empid)FROM employee WHERE managerid=@managerid
 IF @count=1
 BEGIN
  SELECT @empid=empid FROM employee WHERE managerid=@managerid
  INSERT Into @temporder Values(@empid,@managerid)
  INSERT Into @temporder SELECT * FROM dbo.fn_recuursivesample(@empid,1)
 END
ELSE IF @count>1
  BEGIN
   SELECT @empid=min(empid)FROM employee WHERE managerid=@managerid
   WHILE @empid>0
        BEGIN
           Insert Into @temporder Values(@empid,@managerid)


           Insert Into @temporder 
           SELECT * FROM dbo.fn_recuursivesample(@empid,1)


           SELECT @next_empid=isnull(min(empid),0)
           FROM employee
           WHERE empid >@empid and managerid=@managerid
        SET @empid=@next_empid
   END
END
RETURN
END


Syntax to execute the above function


SELECT employeeid, managerid FROM dbo. fn_recuursivesample(1,0)


Result:-


Employeeid        Managerid
     1                            0
     2                            1
     5                            2
     6                            5
     8                            6
     7                            5
     3                            1
     4                            4
3. We can use the Function in the Join queries.


4. We can used UDFs as the parametrized view(a view which take input parameters).


5. UDFs also reduce the compilation cost of SQL codes by caching the plans and reusing them for repeated executions which mean it does not need to be re-parsed and recompiled with each use and it result in better execution time.


6. We can use the WHERE clause to filter the rows as per our needs FROM the result set return by the Function. For example, in the above function fn_recuursivesample, if we want to get the empid of only those employee which are directly under the employee whose empid is given AS the input parameter, the we can use the "WHERE" clause to filter the dataset return by the function.


SELECT employeeid, managerid FROM dbo. fn_recuursivesample(1,0) WHERE managerid=1


It will give the following result:-


Employeeid   Managerid
2                       1
3                       1
4                       1


7. UDFs can be used to divide the complex code into shorter and simple blocks of code which helps in maintenance of the code.


Disadvantages of the UDF
  1. We cannot use temporary tables inside the UDF.
  2. We cannot use Print command inside the UDF.
  3. We can't use the Insert, update and delete command inside the function. We can only use these commands on the table variable defined inside the function.
  4. We cannot use the try catch statement inside the function for the debugging purpose which makes it difficult to debug.
  5. We can't use transaction inside the function.
  6. We can't call the stored procedure from inside the function.
  7. We can't return multiple record sets from a function as we can do in case of Stored Procedure.
  8. We can't use Getdate() function  within a UDF.
Conclusions
    Functions are the feature which is given by the SQL Server to make our complex code shorter and less complex. We can either use the Functions which are already provided by the SQL Server known as the System defined Function such as Avg (), Min (), Max () or we can write our own UDF. If we want to get only a scalar value as result, we can create Scalar UDFs or if we want to get a ResultSet, then we can create Inline Table UDFs or Multi statements UDFs. We can also used Functions in the SELECT statement and in join statements. But while creating the UDFs we should also keeps in our mind that no DML(Insert, Update, Delete) statement can be performed inside the UDF. Also we can't use temporary table inside the Function. Also we can't use transaction management inside the UDF.

DMCA.com