Stored Procedure, SQL Function, Triggers In Brief

Stored Procedure

 
A stored procedure is a set of pre-compiled Structured Query Languages (SQL), so it can be reused and shared by multiple programs. It can access or modify data in a database.
 
Syntax
  1. Create proc Proc_name  
  2. @permater  
  3. as begin  
  4. ----Query here  
  5. end  
OR
  1. Create proc Proc_name  
  2. @permeter datatype  
  3. @rslt output datatype  
  4. as begin  
  5. ----Query here  
  6. select @rslt  
  7. end  

SQL Funcion

 
A function is a database object in SQL Server. Basically, it is also a set of SQL statements that accept only input parameters and produce output in a single value form or tabular form.
 
Syntax
  1. create function funname(@parmeter datatype)  
  2. returns Returntype   
  3. as    
  4. begin Returntype  
  5. end   

Trigger

 
A trigger is also a set of SQL statements in the database which automatically execute whenever any special event occurs in the database, like insert, delete, update, etc.
 
Syntax
  1. create trigger trigger_name   
  2. before | after    
  3. {insert | update | delete}    
  4. on table_name    
  5. for each row    
  6. ---Query here   

Difference between Stored Procedure, SQL Function, and Trigger

 
Executable
 
Store procedure: We can execute the stored procedures when required.

Function: We can call a function whenever required. Function can't be executed because a function is not in pre-compiled form.

Trigger: Trigger can be executed automatically on specified action on a table like, update, delete, or update.
 
Calling
 
Stored procedure: Stored Procedures can't be called from a function because functions can be called from a select statement and Stored Procedures can't be called from. But you can call Store Procedure from Trigger.

Function: Function can be called from Store Procedure or Trigger.

Trigger: Trigger can’t be called from Store Procedure or Function.
 
Parameter
 
Store procedure: Stored Procedures can accept any type of parameter. Stored Procedures also accept out parameter.

Function: Function can accept any type of parameter. But function can’t accept out parameter.

Trigger: We can’t pass a parameter to trigger.
 
Return
 
Store procedure: Stored Procedures may or may not return any values (Single or table) on execution.

Function: Function must return any value.

Trigger: Trigger never return value on execution.
 

Important Facts

 
Q. Can we call the stored procedure inside a user-defined function (UDF)?
 
A: No, we can't use a stored procedure inside UDF.
 
Q. Why can we not execute a stored procedure inside a function in SQL Server?
 
A: Stored Procedures can't be called inside a function because functions would be called by a select command and Stored Procedures couldn't be called by select command. And Store Procedure only execute by using exec/execute.
 
Q. Can we call store procedure in the trigger?
 
A: Yes, we can call stored procedure inside the trigger.
 
For example:
  1. CREATE TRIGGER tri ON tbl FOR INSERT AS  
  2. EXEC mysp  
Q. Can we call UDF inside stored procedure?
 
A: Yes, we can call stored procedure inside the trigger.
 
For example:
  1. Create PROCEDURE [dbo].[callingFunction]  
  2. AS  
  3. begin  
  4.   select dbo.functionname(@perm)  
  5. end  

Summary

 
In this article, I have explained some of the differences and important points related to stored procedure, functions, and trigger in brief.