Sunday , 25 June 2017
Home » SQL » What is a Stored Procedure

What is a Stored Procedure

What is a Stored Procedure

what is a stored procedure

What is a Stored Procedure? Stored procedures are a group of SQL Statement that run in a single execution plan. Stores procedures are created and saved in the database, so you can reuse it whenever you need it.
They can accept input parameter and reduce the network traffic. Stored procedures are good for security and they prevent sql injection.

How to create a simple stored procedure? See below the snippet code for creating a simple stored procedure.

You can execute stored procedure in several ways. The first is by using SQL Server Management Studio. Right click on your stored procedure and click execute stored procedure.
Another way is by typing the query. See the query below.

You can delete stored procedure using SQL Server Management Studio or using the drop command in sql.

There are 4 types of stored procedures:

  • system stored procedures
  • user defined stored procedures
  • extended stored procedures
  • CLR stored procedures.

System Stored Procedure

System stored procedures are stored in the master database and these are starts with a ‘sp_’ prefix. These procedures support sql server functions for external application calls in the system tables.

User Defined Stored Procedure

These procedures are created by user and these are stored in SQL Server. These procedures can These can be created in all system databases except the Resource database or in a user-defined database.

Extended Stored Procedure

Extended procedures provide an interface to external programs for various maintenance activities. These extended procedures starts with the ‘xp_’ prefix and stored in Master database.

CLR Stored Procedure

CLR stored procedure are special type of procedure that are based on the CLR (Common Language Runtime) in .net framework. CLR integration allow for procedure to be coded in one of .NET languages.

Check Also

Stored Procedure vs Functions

Stored Procedure vs Functions In this article I am going to show you the differences …

Leave a Reply

Your email address will not be published. Required fields are marked *