MONTH START OFFER: Flat 15% Off with Free Self Learning Course | Use Coupon MONTH15

- SQL Server Blogs -

How to Create Stored Procedure & Trigger in SQL Server



Difference between stored procedures and triggers

  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.

Introduction

Stored Procedures and Triggers are considered as a vital part of the SQL database. Stored Procedure is used to perform a specific task within a database app. A stored procedure can be used anywhere as per the convenience and saves coding effort and the overall programming time. 

A trigger is a special kind of stored procedure-one that cannot be executed explicitly, instead of attached to an event. Whenever the event takes place, the trigger fires and the trigger's code runs. The objective of this blog is to discuss stored procedures and triggers in SQL Server. 

Also, we will discuss how they are different from each other. If you are planning to become a SQL Server pro then don’t forget to master these two database concepts in 2020 before you start applying for jobs.

Read: The Evolution of SQL Server Versions and Editions

What is Stored Procedure?

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

How to create a 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

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

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. The system stored procedure has a prefix of sp_.

Read: Top 50 MongoDB Interview Questions and Answers

User-defined Stored procedure

User-defined Stored procedures are the procedures created by the 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.

Stored procedure without input parameter

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 an 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:

Read: Job Roles and Responsibilities of a SQL Server Developer you Need to Know


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 a trigger?

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

To know more about Triggers and Stored Procedures in SQL Server, avail our self-learning courses at an attractive discount and learn everything about SQL Server in detail.

How to create triggers?

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

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

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 the following subcategories:

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

Read: How to Download and Install Microsoft SQL Server Data Tools?

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

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:


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

What’s Next?

The blog “Stored Procedures and Triggers in SQL” gives you a depth idea of two important database concepts that are frequently used by programmers. As a database expert, you should know how to use stored procedures, functions, and databases in database apps. In case of any confusion, take online SQL Server training first before you start applying for jobs. 

Register for a Demo class first to get a clear idea of the online learning environment and our certified mentors.

Read: The Evolution of SQL Server Versions and Editions

SQL Tutorial Overview


    Janbask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


Comments

Trending Courses

AWS

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

Upcoming Class

-1 day 04 Dec 2020

DevOps

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

Upcoming Class

7 days 12 Dec 2020

Data Science

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

Upcoming Class

4 days 09 Dec 2020

Hadoop

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

Upcoming Class

6 days 11 Dec 2020

Salesforce

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

Upcoming Class

-1 day 04 Dec 2020

QA

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

Upcoming Class

0 day 05 Dec 2020

Business Analyst

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

Upcoming Class

-1 day 04 Dec 2020

MS SQL Server

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

Upcoming Class

-1 day 04 Dec 2020

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

-1 day 04 Dec 2020

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

0 day 05 Dec 2020

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

15 days 20 Dec 2020

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

14 days 19 Dec 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews