How to use Stored Procedure in SQL or Database? Pros and Cons with Example

Hello folks if you want to learn about Stored Procedures in SQL and how it can help you to create a layer of abstraction around functionality then you have come to the right place.  With the help of many examples, we will learn how to construct stored procedures in SQL Server.

A SQL Server stored procedure is a collection of statements saved in the database as a logical entity. The stored procedure takes the parameters and runs the T-SQL statements, returning the result set if there is one. We now know what is a stored procedure in SQL. But, why use them? What are their benefits? Let's see and search for the answer to these questions.

Benefits of using Stored procedures

It's simple to change: We may change the code inside the stored procedure without having to restart or deploy the program. If the T-SQL queries are written in the application, for example, and the logic has to be changed, we must alter the code in the application and re-deploy it. By putting the code in the database, SQL Server Stored Procedures avoid these issues. As a result, we can simply use the ALTER Process command to update the logic within the procedure.

Only the procedure name is delivered over the network instead of the entire T-SQL code when we utilize stored procedures instead of writing T-SQL queries at the application level.

Stored procedures are reusable because they may be used by different users or client apps without having to rewrite the code.

Security: By removing direct access to the tables, stored procedures lessen the threat. We may also encrypt stored procedures as they're being created, making the source code inside them invisible. To decode encrypted stored procedures, use third-party software like ApexSQL Decrypt.

When the SQL Server stored procedure is run for the first time, it generates a plan and stores it in the buffer pool so that it may be reused the next time it is run.

Let's understand this by using an example. Let's create a few tables and put some data and use it for a stored procedure.

Creating database

CREATE TABLE Product
(ProductID INT, ProductName VARCHAR(100) );
 
CREATE TABLE ProductDescription
(ProductID INT, ProductDescription VARCHAR(400) );
 
INSERT INTO Product VALUES (680,'HL Road Frame - Black, 58')
,(706,'HL Road Frame - Red, 58')
,(707,'Sport-100 Helmet, Red');
 
INSERT INTO ProductDescription VALUES (680,'Replacement mountain wheel 
 for entry-level rider.')
,(706,'Sturdy alloy features a quick-release hub.')
,(707,'Aerodynamic rims for smooth riding.');

Creating a stored procedure

CREATE PROCEDURE GetProductDesc
AS
BEGIN
SET NOCOUNT ON
 
SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM 
Product P
INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID
 
END

The result of the stored procedure would be something like the below when executed.

Creating a stored procedure with parameters

Let's build a SQL Server stored procedure that takes input parameters and processes records depending on those parameters.

The saved method that accepts the argument is shown below.

CREATE PROCEDURE GetProductDesc_withparameters
(@PID INT)
AS
BEGIN
SET NOCOUNT ON
 
SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM 
Product P
INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID
WHERE P.ProductID=@PID
 
END

To execute any stored procedure, we can use the 'exec' keyword. To execute the above-stored procedure, use the below statement: 

EXEC GetProductDesc_withparameters 706

The result of execution will be as below: 

Creating a stored procedure with a default value

We will use the same example as above, just the procedure will change. 

CREATE PROCEDURE GetProductDesc_withDefaultparameters
(@PID INT =706)
AS
BEGIN
SET NOCOUNT ON
 
SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM 
Product P
INNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID
WHERE P.ProductID=@PID
 
END

When the aforementioned operation is run without a parameter value, the default value of 706 is utilized. However, when the value is supplied, the default value is disregarded and the passed value is treated as a parameter.

Creating an encrypted stored procedure

By using the "ENCRYPTION" option while constructing the stored procedure, we may hide the source code.

An encrypted stored method is seen below.

CREATE PROCEDURE GetEmployees
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON 
 
SELECT EmpID,EmpName from Employee
END

When we use sp_helptext to look at the code of the SQL Server stored procedure, we get the message "The text for object 'GetEmployees' is encrypted."

Conclusion

In this article, we explored SQL Server stored procedures with different examples. we have seen many kinds of stored procedures including parameters, default values, and encrypted too. We also have gone through the benefits of stored procedures. This will help you guys use and utilize a stored procedure. Hope you guys enjoyed it. see you in the next article!

Other SQL and Database Articles you may like:

• 4 Examples of CASE expression in SQL Server
• How to use Stored Procedure in SQL?
• How to use WHERE and HAVING clause in SQL
• Difference between Self and Equi join in SQL
• Difference between UNION and UNION ALL in SQL
• What are T-SQL Window Functions?
• MySQL vs NoSQL comparison
• 10 Examples of ALTER command in SQL
• How to find top 10 records in a table?

Thanks for reading this article so far. IF you like it then please share and if you have any questions, feel free to ask.