SQL Stored Procedures – An Ultimate Guide

Filed Under: SQL
SQL STORED PROCEDURE (2)

Hey, folks! In this article, we will be focusing on SQL Stored Procedures.


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.

We have created a Table ‘Info’ using SQL CREATE and INSERT query with data columns as ‘id’ and ‘Cost’. SQL SELECT statement is used to display the contents of the table ‘Info’.

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;

Output:

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:

Syntax:

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.

Example:

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:

Syntax:

EXEC procedure-name;

So, everytime we call the SQL Stored Procedure by SQL EXEC query, it executes the entire SQL block at once.

Example:

EXEC show;

Output:

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.

Syntax:

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.

Example:

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";

Output:

Cost
100

SQL Stored Procedures with Multiple Parameters

SQL Stored Procedures occupy multiple parameters of the database by using a SQL AND operator.

Syntax:

CREATE PROCEDURE procedure-name @variable1 data-type, @variable2 data-type
AS
SELECT * FROM Table
WHERE column1 = @variable1 AND column2 = @variable2;

Example:

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

Conclusion

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.

References

Leave a Reply

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

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages