Stored procedure is group of t-sql statements that are stored as compiled form in the database to perform specific task.
Syntax : CREATE PROCEDURE Procedure_name
( Inputparameter datatype, Outputparameter datatype OUT, ) AS BEGIN SQL -Statements END
EXEC Procedure_name OR EXECUTE Procedure_name
Stored procedures can be classified into following categories:
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_.
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
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 )
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)
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
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'
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:
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
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
CREATE TRIGGER trigger_name ON
Different types of triggers
Triggers are classified into following categories:
DML triggers are fired in response to DML events such as insert, update, delete operations. DML triggers are further subdivided into following subcategories:
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)
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'
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.
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'
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
Difference between stored procedure and triggers
|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.|
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.
Receive Latest Materials and Offers on SQL Server Course