Stored Procedures and Functions :
Following are the difference between Stored Procedure and Function in SQL Server.
Example:
CREATE PROCEDURE dbo.spGetProducts
as
Begin
select * from Product
End
Execute dbo.spGetProducts
1. Stored Procedure can return zero or n values where function return one value which is mandatory.
2. Stored Procedure can have input/output parameters for it where as functions can have only input parameters.
3. Procedure allows select as well as DML statement in it where as function allows only select statement in it.
4. Function can be called from procedure where as procedure cannot be called from function.
5. Exception can be handled by try-catch block in a procedure where as try-catch block can not be used in a function.
6. We can go for transaction management in procedure where as we can't go in function.
Trigger :
Trigger :
1. It is a special type of event driven stored procedure.
2. Trigger initiated when Insert, Delete or Update event occurs.
3. Trigger can call stored procedure.
4. Trigger executed automatically when an Inset ,Update or
Delete operation takes place on a table.
5. It is generally used to implement business rules,auditing.
6. 32 levels for nested triggers.
No comments:
Post a Comment