Hey, folks! In this article, we will be focusing on SQL Stored Procedures.
Table of Contents
What is a Stored Procedure?
Stored Procedures can be considered as Macros of System Programming. An SQL Stored Procedure is a customized and pre-defined SQL code that can be used at any point in time during the execution in the database by calling the function accordingly.
SQL Stored Procedures enable the reusability of code and help save a lot of execution time.
For example, let us consider a website’s login database wherein for any utility function it is necessary to preview the login details of a user. For the same, we can create a Stored Procedure containing an SQL SELECT statement to view the login details.
Thus, every time the database administrator wants to preview the login details of a user, that can be easily done by calling the Stored Procedure.
This saves the overhead of executing the same SQL query again and again.
Creating an SQL Stored Procedure
SQL Stored Procedures are actually defined as SQL code blocks to be called at the time of execution.
Creating a DataBase:
create table Info(id integer, Cost integer); insert into Info(id, Cost) values(1, 100); insert into Info(id, Cost) values(2, 50); insert into Info(id, Cost) values(3, 65); insert into Info(id, Cost) values(4, 97); insert into Info(id, Cost) values(5, 12); select * from Info;
1 100 2 50 3 65 4 97 5 12
Having created the database Table, let us create a SQL Stored Procedure using the below command:
CREATE PROCEDURE procedure-name AS <SQL Commands> GO;
The GO statement is not a mandatory query. It helps the entire Stored Procedure block to execute separately and at once by the compiler.
CREATE PROCEDURE show AS SELECT * FROM Info GO;
Execution of a Stored Procedure
SQL Stored Procedures can be called anywhere within the entire program execution through the below command:
So, everytime we call the SQL Stored Procedure by SQL EXEC query, it executes the entire SQL block at once.
1 100 2 50 3 65 4 97 5 12
Stored Procedure with a Single Parameter
SQL Stored Procedures work with parameters as well. We can create Stored Procedures with one or more parameters and then call the procedures by providing the parameters to it.
CREATE PROCEDURE procedure-name @variable data-type AS SELECT * FROM Table WHERE column = @variable;
Here, we have used SQL WHERE clause to put restrictions to the execution of stored procedure where only those data values will be displayed which satisfies the mentioned condition.
CREATE PROCEDURE disp @Cost nvarchar(30) AS SELECT * FROM Info WHERE Cost = @Cost;
In the above example, we have created a stored procedure with a single parameter declared as ‘Cost’ with data type nvarchar. We applied a condition through WHERE clause according to which those values whose ‘Cost’ parameter value = “100” are displayed.
EXEC disp @Cost = "100";
SQL Stored Procedures with Multiple Parameters
SQL Stored Procedures occupy multiple parameters of the database by using a SQL AND operator.
CREATE PROCEDURE procedure-name @variable1 data-type, @variable2 data-type AS SELECT * FROM Table WHERE column1 = @variable1 AND column2 = @variable2;
CREATE PROCEDURE Multi_disp @Cost nvarchar(30), @id INTEGER AS SELECT * FROM Info WHERE Cost = @Cost AND id = @id;
So, in this example we have created a SQL Stored Procedure with two parameters ‘id’ and ‘Cost’.
EXEC Multi_disp @Cost = "100" , @id = "1";
As seen, when the compiler encounters the above execution statement, it displays only those data values whose id = 1 and cost = 100.
id Cost 1 100
Thus, we have come to the end of this topic. Please feel free to comment in case you come across any doubt and for more such articles related to SQL, please do visit SQL JournalDev.