Difference between Stored Procedure, Trigger and Functions

Difference between Stored Procedure and Trigger:

A stored procedure is a group of Transact-SQL statements that is compiled one time, and then can be executed many times. This increases performance when the stored procedure is executed because the Transact-SQL statements do not have to be recompiled.

A trigger is a special type of stored procedure that is not called directly by a user. When the trigger is created, it is defined to execute when a specific type of data modification is made against a specific table or column.

A CREATE PROCEDURE or CREATE TRIGGER statement cannot span batches. This means that a stored procedure or trigger is always created in a single batch and compiled into an execution plan.



Difference between a function and a stored procedure:

1. Functions can be used in a select statement where as procedures cannot

2. Functions takes only input parameters, where as Procedure takes both input and output parameters.

3. Functions cannot return values of type text, ntext, image & timestamps where as procedures can.

4. Functions can be used as user defined datatypes in create table but procedures cannot
Eg:-create table (name varchar(10),salary getsal(name))