Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Benefits of using a Stored Procedure
3.
Creating a Simple Stored Procedure
4.
Creating a Stored Procedure with Parameters
5.
Creating a Stored Procedure with Output Parameter
6.
Creating a Temporary Procedure
7.
Modifying the Stored Procedure
8.
Renaming the Stored Procedure
9.
Frequently Asked Questions
9.1.
What are the reasons why stored procedures should not be used?
9.2.
Can stored procedures be reused?
9.3.
Functions versus stored procedures: What's the difference?
9.4.
How are stored procedures and user-defined functions different?
9.5.
What are the advantages of stored procedures over queries?
10.
Conclusion
Last Updated: Mar 27, 2024

Sql Server Stored Procedures

Author vishal teotia
0 upvote
Master Python: Predicting weather forecasts
Speaker
Ashwin Goyal
Product Manager @

Introduction

Our purpose in this blog is to demonstrate how to create SQL Server stored procedures using different examples.

A stored procedure in SQL Server is batch of logically grouped statements that are stored in the database. The stored procedure accepts parameters and runs the T-SQL statements in the procedure, and if any results are returned, it returns them.

Benefits of using a Stored Procedure

It can be easily modified: Modifying the stored procedure code does not require restarting or deploying the application. We must edit the code in the application and re-deploy it if we need to modify T-SQL queries that are in the application. By storing the code in the Database, SQL Server Stored Procedures eliminate such challenges. As a result, when we wish to alter the logic inside a procedure, we can simply use the ALTER PROCEDURE statement.

Reduced network traffic: Rather than passing the entire T-SQL code over the network when using stored procedures, only the procedure name is sent.

Reusable: Stored procedures can be used by multiple users or client applications without requiring them to be rewritten.

Security: Stored procedures reduce threats by removing direct access to tables. Stored procedures can also be encrypted when they are created to prevent source code from being seen. Decrypt the encrypted stored procedures with third-party tools, such as ApexSQL Decrypt.

Performance: In SQL Server, when a stored procedure is executed for the first time, a plan is created and stored in the buffer pool, so that that plan can be reused next time.

As part of the examples in this article, I am creating sample tables.

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

Recommended Topic About, procedure call in compiler design

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Creating a Simple Stored Procedure

This example shows how to create a stored procedure that joins two tables and returns the results.

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


To execute stored procedures, we can use 'EXEC ProcedureName'. Using the procedure GetProductsDesc, we get the following results.

Creating a Stored Procedure with Parameters

In this example, we will develop a stored procedure that accepts input parameters and processes records based on those input parameters.

In the following example, the parameter is accepted by a stored procedure.

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


The input parameter must be passed to the stored procedure while it is being executed. The result set can be seen in the image below.

Creating a Stored Procedure with Output Parameter

In the example below, there is an output parameter for a stored procedure. When an employee is inserted, the EmpID column is an auto-identification column.

CREATE TABLE Employee (EmpID int identity(1,1),EmpName varchar(500))
CREATE PROCEDURE ins_NewEmp_with_outputparamaters
(@Ename varchar(50),
@EId int output)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO Employee (EmpName) VALUES (@Ename)
SELECT @EId= SCOPE_IDENTITY()
END


Using output parameters to execute stored procedures is a bit different. In order to store the value returned by the output parameter, we need to declare a variable.

declare @EmpID INT
EXEC ins_NewEmp_with_outputparamaters 'Andrew', @EmpID OUTPUT
SELECT @EmpID

Creating a Temporary Procedure

Temporary procedures can also be created, just like temporary tables. Temporary procedures come in two types, a local temporary stored procedure and a global temporary stored procedure.

In the tempdb database, these procedures are created.

Local temporary Sql Server stored procedures: They are created with # as prefix and are accessible only in the session where they were created. Closing the connection automatically drops this procedure.

A local temporary procedure is shown in the following example.

CREATE PROCEDURE #Temp
AS
BEGIN
PRINT 'Local temp procedure'
END


Global temporary SQL Server stored procedure: In addition to being accessed on other sessions, these procedures are created with ## as prefix. Upon closing the connection used to create this procedure, the procedure is automatically dropped.

A global temporary procedure is shown in the following example.

CREATE PROCEDURE ##TEMP
AS
BEGIN
PRINT 'Global temp procedure'
END

Modifying the Stored Procedure

Modify an existing stored procedure with the ALTER PROCEDURE statement. The following example shows how this can be done.

ALTER 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

Renaming the Stored Procedure

The system stored procedure sp_rename can be used to rename a stored procedure using T-SQL. Here is an example of renaming the procedure "GetProductDesc" to "GetProductDesc_new". 

sp_rename 'GetProductDesc','GetProductDesc_new'

Must Read SQL Clauses

Frequently Asked Questions

What are the reasons why stored procedures should not be used?

Especially with stored procedures, you are encouraging bad development practices, since you have to list out each field in a database table half a dozen or more times. A single column added to your database table is a huge pain. 

Can stored procedures be reused?

It is possible to execute stored procedures from multiple clients without having to write the code again.

Functions versus stored procedures: What's the difference?

It is optional in Stored Procedures for the function to return a value. Even procedures can return zero or n values.

How are stored procedures and user-defined functions different?

It is only possible to read select statements with the user-defined function, while DML statements are not allowed.

What are the advantages of stored procedures over queries?

After queries are submitted, they are compiled and executed. Unlike queries, stored procedures are compiled when submitted for the first time, & this compiled content is stored in something called procedure cache, so subsequent calls will not require compilation, just execution.  

Conclusion

Throughout this article, we examined different SQL Server stored procedures with examples. Please feel free to ask any questions below in the comment sect⁠⁠⁠⁠⁠i⁠⁠⁠⁠⁠⁠on.

Check out this link if you want to learn SQL.

Refer to our Guided Path on Coding Ninjas Studio to upskill yourself in MongoDBCompetitive ProgrammingDatabasesSystem Design and many more!

Do upvote our blogs if you find them helpful and engaging!

Happy Learning!!

Previous article
SQL Server Indexes
Next article
SQL Server User-Defined Functions
Live masterclass