Mastering SQL Stored Procedures: A Comprehensive Guide

Unlock the Power of SQL Stored Procedures with Our Comprehensive Guide - Master the Art of Database Optimization and Efficiency Today.
Mastering SQL Stored Procedures

Introduction - Definition of Stored Procedure

A stored procedure is a readily Witten SQL code that we'll be save, so the sql query could be reutilized anytime. So if you have possessed an SQL query that you need to write repeatedly, just save the same as a stored procedure, and you can call it anytime to execute it as per requirement.

Also, you can supply parameters to a specific stored procedure, in order to get expected result.

Syntax for basic LLP Procedure 

Execute a Stored Procedure

Demo SQL Database

Following is a result from the "Customer" table in the Business sample database:

CustomerID CustomerName Contact Person Address PostalCode Country
James Collingwood James Collingwood Obere Str. 69 Berlin 12210 Germany
Antonio anderson Antonio anderson Avda. de la Constitución 3333 México 5022 Mexico
Calling the Horn Michael Hardy Mataderos 2315 México D.F. 5023 Mexico
Minzes snabbköp Ninzs Berglund 121 Hanover Sq. London WA1 2DP UK
Tina Smith Tina Smith Berguvsvägen 9 Luleå S-958 24 Switzerland

Example of SQL Stored Procedure 

The following SQL codes makes a stored procedure named "SelectCustomer" that fetches all information from the "Customer” table:


Execute the  above stored procedure  as follows:


Execute the stored procedure above as follows:


Stored procedure type

According to no. of parameters, we can divided store into two types as follows.

Stored Procedure With One Parameter

The following SQL statement creates a stored procedure that selects Customers from a particular City from the "Customers" table:


Run the above stored procedure as follows:


Stored Procedure With Multiple Parameters

Passing multiple parameters to a stored procedure is very easy. Just list every parameter and data type separated by putting comma as follows.

The following SQL code makes a stored procedure that selects Customer details from a specific City with a specific PostalCode from the table "Customer" :

Run the above stored procedure as follows:


Types of SQL stored procedure parameters

Often, the stored procedures contains parameters. The parameters increase the utility of stored procedure and make reusable. Mainly there are 3 parameters such as IN,OUT, or INOUT.

IN type sql parameters

IN type parameter is the default type. Whenever we define an IN type parameter in a stored procedure, the program of calling, needs to pass a specific parameter to the stored procedure.

Additionally, the value given to an IN parameter is safe guarded. It means that even you alter the IN parameter’s value within the stored procedure, its initial value won’t be changed after the end of stored procedure. It means, the stored procedure only performs on the IN parameter’s copy.

OUT type SQL parameters

The value of a specific OUT parameter can be altered within the stored procedure and the new value of same is forwarded to the calling program.

Note that the stored procedure will not be able to get the beginning value of the OUT parameter while it starts.

INOUT parameters

An INOUT parameter is a joint of two parameters, IN and OUT . Hence, it indicates that the calling program might pass the argument, and also the stored procedure can change the parameter INOUT, and send the new value back to the respective calling program.

What do you mean by a parameter?

Here is the genuin syntax of explaining a parameter in specific stored procedures:

Code language: SQL 

In the following syntax,

  • Step-1: Mention the parameter mode, that can be OUT or IN or INOUT according to the target of the parameter in the particular stored procedure.
  • Step-2: Mention the parameter’s name. The parameter’s name needs to follow the column naming rules of the SQL.
  • Step-3: Mention the type of data and length (maximum) of the parameter.

SQL stored procedure parameter 


Utilizing parameters of stored procedure.

Example of IN parameter 

Following stored procedure helps find all offices which identify in a country mentioned by the input parameter : countryName:

In order to obtain offices situating in the USA, you have to give an argument (USA) to the stored procedure as follows:

To obtain address of offices in France, you can pass the common string France to the stored procedure ‘GetOfficeByCountry’ as follows:

Since the countryName is available in the IN parameter, you must give an argument. If you are not doing so, you’ll have an error:

Here’s the error:

ErrorCode: 1318.Incorrect number of arguments for PROCEDURE;

Advantages :

The major benefits of stored procedure are mentioned below:

  1. Better output – Definitely, the procedure calls are fast and helpful  as stored procedures are created once and kept in executable structure. Hence a faster response can be obtained. The code that is executable automatically cached, hence reduces the need of memory.
  2. Ease of utilize – To write a stored procedure, one can utilize any Java (IDE) Integrated Development Environment. Then, they could be deployed on any tier of architecture of network.
  3. Scalability – Stored procedures enhance scalability by isolating app handling on the server.
  4. Better Productivity – Stored Procedure often assists in encapsulating both the business and SQL logic due to which it facilitate and modularity and reusability.
  5. Maintainability – Maintaining a process on a server is much smoother than maintaining copies on different client machines, as scripts are positioned in one location.
  6. Privacy and Security – Interacting Oracle data can be prohibited by granting users to update the information only through stored procedures that run with their privileges of definer.

Drawbacks :

Following are the drawbacks of stored procedures:

  1. Testing – Testing of a logic of a stored procedure is very critical. Any information errors in managing stored procedures are not produced until runtime.
  2. Versioning – The stored procedure doesn’t support Version management.
  3. Debugging – Debugging stored procedures is either be very complex or impossible at all. Some relational databases like SQL Server possesses some debugging abilities.
  4. Cost – Some additional developers as DBA might be needed to access the SQL and make a better stored procedure. Automatically, this needs additional cost.
  5. Portability – Often, the Complex stored procedures don’t port to latest higher versions of the same particular database. This is particularly true in case of shifting from one type of database (Oracle) to another type of database (MS SQL Server).

Renaming stored Procedure

By running following code, we can rename stored procedure.

Modifying stored procedure

In order to change the stored procedure, just right click on it and select Modify to bring the stored procedure in new window of query with ALTER statement.

Now, you will be able to change the stored procedure (changing means addition / deletion / modification of the parameters and its category, changing the SQL statements etc.) and click on Execute icon that is located on the toolbar or press F5 key from the keyboard which will update the name of the stored procedure.


Hope, the readers must have a brief knowledge about stored procedure and can work with stored procedure flawlessly. As stored procedure is a significant time saving database interaction program in application development program, the developers need to have proper skill on this.


Anjan kant

Outstanding journey in Microsoft Technologies (ASP.Net, C#, SQL Programming, WPF, Silverlight, WCF etc.), client side technologies AngularJS, KnockoutJS, Javascript, Ajax Calls, Json and Hybrid apps etc. I love to devote free time in writing, blogging, social networking and adventurous life

Post A Comment: