Stored Procedure in SQL

What are Stored Procedure in SQL?

Stored Procedures are used to perform one or more database DML actions. It's just a collection of SQL statements that take some input in the form of arguments, perform some work, and may or may not return a result.
It can conduct one or more DML operations on the database and return a value, depending on the statements in the procedure and the arguments you pass. As a result, it enables reusability by allowing you to pass the same statements many times.

As It is a piece of prepared SQL code that you can save and reuse over and over. So, if you have a SQL query that you create frequently, save it as a stored procedure and then call it to run it. You can also pass parameters to a stored procedure so that it can act based on the value(s) of the parameter(s) passed.

Parameters are the most crucial aspect. Values are passed to the Procedure via parameters. The following are three different sorts of parameters:

      IN : This is the procedure's default parameter.The values are always received from the caller programme.
      OUT: The values are always sent to the calling application when this parameter is set.
      IN OUT: This parameter performs both operations.It receives values from the caller application and sends them to it.

Stored Procedure Syntax

In SQL, creating a stored procedure is as simple as it gets. The SQL stored procedure syntax is as follows:

CREATE or REPLACE PROCEDURE name(parameters)
IS
variables;
BEGIN
//statements;
END;

:Let's take one example for better understanding,

The following SQL statement generates the "SelectAllCustomers" stored procedure, which selects all records from the "Customers" table:

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

Execute the stored procedure above as follows:

EXEC SelectAllCustomers;

Stored Procedure With One Parameter

The following SQL statement generates a stored procedure that searches the "Customers" table for customers from a specific city:

CREATE PROCEDURE SelectAllCustomers @City nvarchar(40)
AS
SELECT * 
FROM Customers 
WHERE City = @City
GO;

Execute the stored procedure above as follows:

EXEC SelectAllCustomers  @City = 'USA';

Stored Procedure With Multiple Parameters

It's simple to set up several parameters. Simply separate each parameter and data type with a comma, as seen below.

The following SQL statement generates a stored procedure that chooses Customers from the "Customers" table who live in a specific city and have a specific postal code:

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * 
FROM Customers 
WHERE City = @City AND PostalCode = @PostalCode
GO;

Execute the stored procedure above as follows:

EXEC SelectAllCustomers  @City = 'USA', @PostalCode = 'WA1 1DP';

What are the benifits of Stored Procedure in SQL?

The following are some of the key advantages of stored procedures:

🚀 Reusable:
As previously stated, stored procedures can be readily used and reused by multiple users and applications by simply calling them.

🚀 Simple to change:
Using the ALTER TABLE command, you can simply change the statements in a stored procedure whenever you choose.

🚀 Security:
Using stored procedures, you can improve the security of an application or database by preventing users from accessing the table directly.

🚀 Low network traffic:
Instead of providing the entire query, the server merely provides the procedure name, which reduces network traffic.

🚀 Increases performance:
When a stored procedure is initially used, a plan is constructed and saved in the buffer pool for speedy execution the following time.

Why Do You Use SET NOCOUNT ON?

As you can see, SET NOCOUNT ON was used to create the stored procedure in this example.
But why is that?
NOCOUNT instructs the server not to count the number of rows that are affected. Setting NOCOUNT to ON will prevent the number of rows affected messages from being displayed. You can also turn it off to observe how many rows are affected.