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.