A stored procedure in SQL is a group of SQL statements that are stored together in a database. Based on the statements in the procedure and the parameters you pass, it can perform one or multiple DML operations on the database, and return value, if any. Thus, it allows you to pass the same statements multiple times, thereby, enabling reusability.
What are the Benefits of using a Stored Procedure in SQL?
Stored procedures provide some crucial benefits, which are:
- Reusable: As mentioned, multiple users and applications can easily use and reuse stored procedures by merely calling it.
- Easy to modify: You can quickly change the statements in a stored procedure as and when you want to, with the help of the ALTER TABLE command.
- Security: Stored procedures allow you to enhance the security of an application or a database by restricting the users from direct access to the table.
- Low network traffic: The server only passes the procedure name instead of the whole query, reducing network traffic.
- Increases performance: Upon the first use, a plan for the stored procedure is created and stored in the buffer pool for quick execution for the next time.
How to Create a Simple Stored Procedure in SQL?
Creating a stored procedure in SQL is as easy as it can get. The syntax of SQL stored procedure is:
CREATE or REPLACE PROCEDURE name(parameters)
AS
variables;
BEGIN;
//statements;
END;
In the syntax mentioned above, the only thing to note here are the parameters, which can be the following three types:
- IN: It is the default parameter that will receive input value from the program
- OUT: It will send output value to the program
- IN OUT: It is the combination of both IN and OUT. Thus, it receives from, as well as sends a value to the program
Note: You will work with and look at examples for different parameters in this article.
You will use the syntax to create a simple stored procedure in SQL. But before that, create two tables using the CREATE TABLE command that you will use throughout the article. You will also insert some values in them using the INSERT INTO command.
CREATE TABLE Car(
CarID INT,
CarName VARCHAR(100)
);
INSERT INTO Car VALUES (101,'Mercedes-Benz');
INSERT INTO Car VALUES (201,'BMW');
INSERT INTO Car VALUES (301,'Ferrari');
INSERT INTO Car VALUES (401,'Lamborghini');
INSERT INTO Car VALUES (501,'Porsche');
SELECT * FROM Car;
Output:
Now create the second table named CarDescription.
CREATE TABLE CarDescription(
CarID INT,
CarDescription VARCHAR(800)
);
INSERT INTO CarDescription VALUES (101,'Luxury vehicle from the German automotive');
INSERT INTO CarDescription VALUES (201,'Luxury motorcycle from the German automotive');
INSERT INTO CarDescription VALUES (301,'Luxury sports car from the Italian manufacturer');
INSERT INTO CarDescription VALUES (401,'Luxury SUV from the Italian automotive');
INSERT INTO CarDescription VALUES (501,'High-performance sports car from the German manufacturer');
SELECT * FROM CarDescription;
Output:
Now that you have created both the tables, start creating the stored procedure in SQL with the syntax mentioned earlier. For the simple procedure, you will have to use the JOIN keyword to join both the tables, and output a new one with CarID, CarName, and CarDescription.
CREATE PROCEDURE GetCarDesc
AS
BEGIN
SET NOCOUNT ON
SELECT C.CarID,C.CarName,CD.CarDescription FROM
Car C
INNER JOIN CarDescription CD ON C.CarID=CD.CarID
END
This will create the stored procedure, and you will see the “command(s) executed successfully” message in Microsoft SQL Server Management Studio. Now, since you have created the procedure, it's time to execute it. The syntax to execute the procedure is:
EXEC procedure_name
Let’s execute the procedure we have created.
EXEC GetCarDesc;
Output:
As you can see in the output, the stored procedure executed the Join statement and gave the desired result.
Why Do You Use SET NOCOUNT ON?
If you have noticed, this example used SET NOCOUNT ON while creating the stored procedure. But why? NOCOUNT tells the server not to count the number of affected rows. Thus, setting NOCOUNT to ON will prevent displaying the messages of the number of rows affected. You can also set it to OFF, to see the number of rows affected.
How to Create a Stored Procedure with Parameters?
In this example, you will have to create a new stored procedure in SQL with parameters.
CREATE PROCEDURE GetCarDesc_Para
(@CID INT)
AS
BEGIN
SET NOCOUNT ON
SELECT C.CarID,C.CarName,CD.CarDescription FROM
Car C
INNER JOIN CarDescription CD ON C.CarID=CD.CarID
WHERE C.CarID=@CID
END
To execute it,
EXEC GetCarDesc_Para 201;
Output:
As you can see, to execute a stored procedure with parameters, you have to pass the expected arguments along with EXEC procedure_name. Like this one, you can also create a stored procedure with multiple parameters.
How to Create a Stored Procedure in SQL with Default Parameters?
You can also create a stored procedure that has default parameters. This means that you will pass the parameter value during the creation itself. Thus, it will take the default parameter value whenever you execute the procedure if the user explicitly does not provide any other value and provide the results accordingly. Suppose you passed a value during execution; it will ignore the default value and give the results accordingly.
CREATE PROCEDURE GetCarDesc_DefPara
(@CID INT = 301)
AS
BEGIN
SET NOCOUNT ON
SELECT C.CarID,C.CarName,CD.CarDescription FROM
Car C
INNER JOIN CarDescription CD ON C.CarID=CD.CarID
WHERE C.CarID=@CID
END
Now you will execute the procedure once passing no external parameter and once with an argument.
EXEC GetCarDesc_DefPara;
Output:
As you can see in the output, it executed the procedure with the default parameter as you didn’t pass any parameter explicitly.
EXEC GetCarDesc_DefPara 401;
Output:
This time as you passed a parameter externally, the procedure ignored the default parameter and took the one you gave.
How to Create a Stored Procedure in SQL With an Output Parameter?
A stored procedure in SQL that has an output parameter will return a value after the execution. A relatable example of using a procedure with output parameters is with the IDENTITY keyword. This keyword automatically generates a unique number for each row you enter. Thus, when you have a vast database and don’t want to remember the last unique number, you insert the primary key. The IDENTITY keyword is the best choice.
In this example, for a change, you will create a Students table and use the IDENTITY keyword in it. You will then make and use a stored procedure that will display the auto-inserted number.
CREATE TABLE Students(
ID INT IDENTITY(1,1),
Name VARCHAR(25),
Age INT
);
This will create the Students table. Now, make a stored procedure that will insert some values and display the auto-inserted value in the ID column.
CREATE PROCEDURE insval_student_outPara
(@Sname varchar(50),
@Sage int,
@SId int output)
AS
BEGIN
SET NOCOUNT ON
INSERT INTO Students (Name) VALUES (@Sname)
INSERT INTO Students (Age) VALUES (@Sage)
SELECT @SId= SCOPE_IDENTITY()
END
This creates the stored procedure in SQL. Now the only thing left is to execute the procedure and get the output parameter. However, executing procedures with output parameters is a bit different. You first have to declare a variable that will hold the output parameter returned by the procedure. Here’s how to execute the procedure you just created.
declare @StuID INT
EXEC insval_student_outPara 'David', 21, @StuID OUTPUT
SELECT @StuID
Output:
How to Create an Encrypted Stored Procedure in SQL?
You can encrypt a stored procedure using the ENCRYPTION keyword while creating the procedure. The keyword will encrypt and hide the source code. When you try to get the source code with the help of the in-built function sp_helptext, the server will respond, saying, “The text for object ‘procedure_name’ is encrypted.” Here’s an example of creating an encrypted stored procedure for our Student’s table.
CREATE PROCEDURE GetStudents
WITH ENCRYPTION
AS
BEGIN
SET NOCOUNT ON
SELECT ID, Name, Age from Students
END
This will create the procedure. Now you will use the sp_helptext to get the source code and see the output.
sp_helptext GetStudents;
Output:
How to Create a Temporary Stored Procedure in SQL?
As the name gives out, temporary procedures help create and use the procedures for a limited time. These are stored in the tempdb database. You can create two different types of temporary procedures: local and global.
Creating Local Temporary Stored Procedure
For creating a local temporary procedure, you need to use the # prefix. These procedures are only accessible until you close the session in which it created them. Thus, when you close a connection, they are automatically dropped. Here’s an example of creating a local temporary stored procedure.
CREATE PROCEDURE #Temporary
AS
BEGIN
PRINT 'This is an example of a local temporary stored procedure'
END
Executing the procedure:
EXEC #Temporary;
Output:
Creating Global Temporary Stored Procedure in SQL
For creating a global temporary procedure, you need to use the ## prefix. These procedures are accessible from other sessions along with the session in which it was created. However, it is still automatically dropped when you close the connection. Here’s an example of creating a global temporary stored procedure.
CREATE PROCEDURE ##Global_Temporary
AS
BEGIN
PRINT 'This is an example of a global temporary stored procedure'
END
Executing the procedure:
EXEC ##Global_Temporary;
Output:
How to Modify a Stored Procedure in SQL?
You can modify a stored procedure just like modifying a table in the database using the ALTER command.
ALTER PROCEDURE GetCarDesc
AS
BEGIN
SET NOCOUNT ON
SELECT C.CarID,C.CarName,CD.CarDescription FROM
Car C
INNER JOIN CarDescription CD ON C.CarID=CD.CarID
END
Output:
As you can see, the output shows “Command(s) completed successfully.” Thus, the GetCarDesc procedure that you created earlier is now modified.
How to Rename a Stored Procedure in SQL?
You can rename a stored procedure in SQL using the in-built procedure sp_rename. The syntax for renaming is:
sp_rename ‘old_name’, ‘new_name’
Using the above syntax, we will rename the GetCarDesc stored procedure that we have altered in the previous section.
sp_rename 'GetCarDesc', 'New_GetCarDesc';
You can confirm the change by executing the stored procedure with the new name.
EXEC New_GetCarDesc;
Output:
As you can see in the output, it executes the stored procedure successfully, confirming the rename.
Master Business Analysis techniques with Purdue University instructors and IBM and EY experts. Enroll now!
Conclusion
In this article, you have learned about stored procedures in SQL. You have also gone through various examples to understand how to perform different operations on and with a stored procedure. Learning and understanding stored procedure is essential as it provides numerous benefits that are vital when it comes to database management. However, it is just a fundamental concept of a vast umbrella. If you want to excel in database management, learning other basic and advanced concepts is also essential. You can opt for Simplilearn’s SQL Certification Training Course. The course provides in-depth coverage of all the important fundamentals, query tools, SQL commands, and self-paced learning material. To sum it up, the course is well adept at helping you excel in SQL database management.
Have any questions for us? Leave them in the comments section of this article. Our experts will get back to you on the same, ASAP!