Stored Procedure, SQL Function, Triggers In Brief

In this blog, I will explain about Stored procedure, SQL function, and 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

Create proc Proc_name
@permater
as begin
----Query here
end

OR

Create proc Proc_name
@permeter datatype
@rslt output datatype
as begin
----Query here
select @rslt
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

create function funname(@parmeter datatype)
returns Returntype
as
begin Returntype
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

create trigger trigger_name
before | after
{insert | update | delete}
on table_name
for each row
---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:

CREATE TRIGGER tri ON tbl FOR INSERT AS
EXEC mysp

Q. Can we call UDF inside stored procedure?

 

A: Yes, we can call stored procedure inside the trigger.

 

For example:

Create PROCEDURE [dbo].[callingFunction]
AS
begin
  select dbo.functionname(@perm)
end

Summary

 

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