Mastering SQL Stored Procedures: A Comprehensive Guide

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:

Example

Execute the  above stored procedure  as follows:

Example

Execute the stored procedure above as follows:

Example

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:

Example

Run the above stored procedure as follows:

Example

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:

Example

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 

Examples

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.

Conclusion

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.