Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

How to Create Stored Procedure & Trigger in SQL Server

What is Stored Procedure?

Stored procedure is group of t-sql statements that are stored as compiled form in the database to perform specific task.

How to create Stored Procedure?

Syntax : CREATE PROCEDURE Procedure_name


 (
	Inputparameter datatype,
	Outputparameter datatype OUT,   
	)
	AS 
	BEGIN
	SQL -Statements
	END

How to execute Stored procedure?

Syntax: EXEC Procedure_name OR EXECUTE Procedure_name  

Different types of Stored procedures

Stored procedures can be classified into following categories:

  1. System Stored procedures
  2. User Defined Stored procedures

System Stored procedures :

System Stored procedures are inbuilt stored procedures created by Microsoft to perform various administrative tasks. Examples of system stored procedures : sp_who, sp_lock, sp_depends etc. System stored procedure has a prefix of sp_.

SQL Server Quiz

User defined Stored procedure :

User defined Stored procedures are the procedures created by user to implement specific business logic. The prefix of user defined stored procedures varies from organization to organization. We can create user defined stored procedures with or without input and output parameters. In this blog we will cover various examples illustrating the stored procedures with or without parameters.

Read More: SQL Server Interview Questions

Stored procedure without input parameter

Read: Top 50 MySQL Interview Questions and Answers

Suppose there is a table named Sales_record whose structure is given below :


 CREATE TABLE Sales_record
(
Country varchar(20),
Product varchar(20),
Quantity INT,
SalesAmount INT
)

 

How to Create Stored Procedure & Trigger in SQL Server Insert the following data into the table Sales_record we created previously:


INSERT INTO Sales_record VALUES 
('USA','Computer',100,50000),
('USA','HardDisk',10,20000),
('USA','FloppyDrive',50,1000),
('INDIA','Computer',1000,500000),
('INDIA','HardDisk',5,10000),
('INDIA','FloppyDrive',100,2000)

How to Create Stored Procedure & Trigger in SQL Server Now we will create stored procedure without parameter which will return the TotalSalesAmount for each country.


CREATE PROCEDURE usp_SalesRecord
AS
BEGIN
SELECT Country, SUM(SalesAmount) AS TotalSalesAmount
FROM Sales_record
GROUP BY Country
END
EXECUTE usp_SalesRecord

How to Create Stored Procedure & Trigger in SQL Server Stored procedure with input parameter

In this example we will query the Sales_record table, but instead of getting back all records we will limit it to just a particular country:


CREATE PROCEDURE usp_SalesbyCountry
(
@Country  varchar(20)
)
AS 
BEGIN
SELECT Country, SUM(SalesAmount) AS TotalSalesAmount
FROM Sales_record 
WHERE [email protected]
GROUP BY Country
END

EXEC usp_SalesbyCountry 'USA'

How to Create Stored Procedure & Trigger in SQL Server Stored procedure with input and output parameters

We can also return the TotalSalesAmount of each country using the output parameter. The following example explains how to achieve so using output parameters:

Read More: How to Clear SQL Server Transaction Log File with DBCC


CREATE PROCEDURE usp_SalesbyCountry_with_out
(
@Country  varchar(20),
@sum_amount int OUT
)
AS 
BEGIN
SELECT @sum_amount=SUM(SalesAmount) 
FROM tbl_Sales2 
WHERE [email protected]
GROUP BY Country
END

DECLARE @sum_amount int
EXEC usp_SalesbyCountry_with_out 'USA',@[email protected]_amount OUT
SELECT @sum_amount AS Total_saleamount_USA

How to Create Stored Procedure & Trigger in SQL Server   What is trigger?

A trigger is a special kind of stored procedure-one that cannot be executed explicitly, instead attached to an event. Whenever the event takes place, the trigger fires and the trigger's code runs.

Read More: How to install Microsoft SQL Server Express

Read: What is the Substring Function in the SQL? Example of SQL Server Substring

How to create triggers?

Syntax: CREATE TRIGGER trigger_name ON {FOR|AFTER|INSTEADOF} {INSERT|DELETE|UPDATE} AS SQL statements

Different types of  triggers

Triggers are classified into following categories:

  1. DML Triggers
  2. DDL Triggers

DML triggers

DML triggers are fired in response to DML events such as insert, update, delete operations. DML triggers are further subdivided into following subcategories:

  1. AFTER triggers or FOR triggers
  2. INSTEAD OF triggers

SQL Server Curriculum

AFTER triggers :

These triggers are fired after SQL Server  insert, update and delete statements completes the execution of the action successfully that fired it. The two special tables: the deleted table and the inserted tables are created when DML trigger statements are fired. Inserted table stores the updated new value after the update on the table and inserted value after the insertion on the table for which that trigger is created. Deleted table stores the old value after the update on the table and deleted value after the deletion on the table for which that trigger is created.

Read More: How to Create Database in SQL Server?

Example of After insert trigger:


CREATE TRIGGER trigger_insert
ON Sales_record
AFTER INSERT
AS
BEGIN 
SELECT * from inserted
END

INSERT INTO Sales_record VALUES 
('USA','Pendrive',1000,5000)

How to Create Stored Procedure & Trigger in SQL Server Example of After update trigger:

Read: How to Use Alter, Drop, Rename, Aggregate Function in SQL Server?

CREATE TRIGGER trigger_update
ON Sales_record
FOR UPDATE
AS
BEGIN 
SELECT * from inserted
SELECT * from deleted
END

 UPDATE Sales_record SET SalesAmount=10000 WHERE Product= 'Pendrive'

How to Create Stored Procedure & Trigger in SQL Server

INSTEAD OF triggers :

In instead of trigger , the statements that was issued to cause the trigger to fire will never runs only the code in the trigger runs. In the following example instead of deleting the row containing the product 'Pendrive' record from Sales_record table, the message in the trigger will be displayed.

Read More: How to Increase the Speed of SQL Query Execution

Example of Instead of trigger:


CREATE TRIGGER trg_delSales_record
ON
Sales_record
INSTEAD OF DELETE 
AS 
BEGIN
Print 'DONT have permission to delete from that table'
END

DELETE from Sales_Record Where Product='Pendrive'

How to Create Stored Procedure & Trigger in SQL Server

DDL triggers

DDL triggers are fire in response to DDL events such as create, alter and drop. In the following example we created a table named test_table in the test_database .When we try to drop any table in the test_database , DDL trigger will fire and will run the code in the trigger.

Read More: How to Restore a Database Backup from SQL


USE [test_database]
CREATE TABLE [dbo].[test_table]
(
	[ID] [int] NULL,
	[Name] [varchar](50) NULL
) 

--creating DDL trigger on dropping of the table
ALTER TRIGGER trg_create
ON 
DATABASE
FOR DROP_TABLE
AS
BEGIN
Print 'Table dropped from test_database'
END

DROP TABLE test_table

How to Create Stored Procedure & Trigger in SQL Server

Difference between stored procedure and triggers

Read: Comparative Study of SQL and NoSQL Databases
  Stored Procedure Trigger
Input and output parameters Can have input and output parameters in stored procedures. Cannot have input and output parameters in triggers.
Execution Stored Procedures can be executed manually. Triggers cannot be executed manually ,they are fired in response to events.
Call Cannot call triggers in stored procedures. Can call stored procedures in triggers.
Inserted and deleted table  Inserted and deleted tables are not created. Inserted and deleted tables are created automatically when trigger is fired.
Return values Stored procedures can return values. Triggers cannot return values.

SQL Tutorial Overview


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

1 day 14 Nov 2019

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

2 days 15 Nov 2019

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

2 days 15 Nov 2019

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

3 days 16 Nov 2019

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

1 day 14 Nov 2019

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

20 days 03 Dec 2019

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

2 days 15 Nov 2019

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

6 days 19 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews