How to Create Advanced Stored Procedures in SQL Server

SQL Query is an important language for database management. It will allow multiple users to share the procedure. Whenever we want to use the particular code again and again, we can go for the stored procedure in SQL Server.

In this SQL Server tutorial, you will learn how to create advanced stored procedures. Let’s get into depth for learning stored procedures in SQL Server.

Table of Contents

What is the Stored Procedures in SQL Server

Before discussing how to create stored procedures, we will first define it and its benefits.

A stored procedure accepts input parameters, performs certain operations, and returns the output values. This allows the user to give different inputs to use or retrieve particular portions of the data.

When one user in the organization modifies the stored procedure, all the users can see the update. Relational Databases (RDBMS) like MySQL and SQL servers support stored procedures.

Benefits of using Stored Procedures in SQL Server

Learn below the major benefits of using stored procedures in SQL Server.

Consider the scenario: You have to use one or multiple queries that will run multiple times at frequent intervals. These queries run with complex logic in our application to apply business logic to the data. They will take the data from the database and then apply the logic to change the data and update the database.

The stored procedure allows you to extend the functionality of databases by writing code blocks to process the data. We can call the database to run the stored procedure where we applied business requirements. This will decrease the network congestion and improve the application’s performance.

A stored procedure is compiled once and used multiple times in the database, whereas an SQL query to the database should be compiled every time. This provides database security, and we can also restrict security by granting the user access to a particular procedure rather than the whole database.

Moreover, the stored procedure hides the table name because the other user will access it using the stored procedure procedure name.

Create Procedures in SQL Server

Follow the below points carefully to create a procedure in SQL Server.

First and foremost, open the SQL Server Management Studio. Once it is opened, make sure it is connected to the database. My database name is imsdb.

Click the New Query and start writing the code in SQL Server Management Studio.

Here, we will be using this imsdb database. Use this syntax as the first query and proceed next.

use imsdb;

Now, use the below syntax to see the details of the table you are choosing here. Here, I am taking the EmpDetails table as an example.

Now, use the below syntax to create a stored procedure and execute it. Allrecords is my procedure name here. Once you click on the execute icon at the top, it will show the Commands completed successfully.

Create Procedure Allrecords
AS
Select * from EmpDetails
Go;

Use the below syntax to execute the created procedure in SQL Server Management Studio.

exec Allrecords;

Now, you will see the below output.

Advanced Stored Procedure in the SQL Server

This is the way to create stored procedures in SQL Server Management Studio.

Add Single Parameter in Stored Procedures

We have seen how to create a procedure; now, we will see how to add a single parameter to the created procedure in SQL Server Management Studio.

Now, we will see who has a 45000 salary for the table EmpDetails we created already. We will pass a single parameter. Below is the syntax.
- Allrecords2 – Procedure name
- @salary money – Here, I want to see the salary and we have to mention the datatype.

Create procedure Allrecords2 @salary money
As
select * from EmpDetails where salary = @salary
Go

In this step, we will execute the query by using the below syntax. Check the below output as well.

exec Allrecords2 @salary = 45000

This is how we have to add a single parameter in the stored procedure from SQL Server Management Studio.

Add Multiple Parameters in the Stored Procedures

Let’s see how to add multiple parameters for the table EmpDetails below.

The syntax will be similar to what we have used for the single parameter above. The only thing we have to add is whatever we require additionally.

Create procedure Allrecords3 @salary money, @Empname varchar(20)
As
select * from EmpDetails where salary = @salary and EmpName = @EmpName 
Go

Here, we have given the name as the second parameter. Check the below output.

Advanced Stored Procedures in SQL Server

We have seen how to create a stored procedure using a select query with single and multiple parameters. Here, we will see how to use an advanced stored procedure in SQL Server using an Insert statement.

I am using the same example table EmpDetails here. Use the syntax below to add the column to the table. EmpAdd is my procedure name. Once added execute the query and proceed further.

Create Procedure EmpAdd
As
Insert into EmpDetails (EmployeeId, EmpName, Role, salary) values (104, 'peter', 'HR', 60000);

Once the query is executed successfully, you can see the name of your recently created stored procedures under Stored procedures below the programmability. Right-click on your stored procedure name and click Modify.

Now, add the below syntax in the ALTER query and click Execute. Here, we are adding the column name with the @ symbol and datatype.

ALTER Procedure [dbo].[EmpAdd] @EmployeeId int, @EmpName varchar(20), @Role char, @salary money
As
Insert into EmpDetails (EmployeeId, EmpName, Role, salary) values (@EmployeeId,  @EmpName, @Role , @salary);

Now, we can see the parameters under the Stored Procedures and the parameters we have added there. If you are working on the project, there is no need to write queries there. If we use stored procedures to write the code, the code speed will be faster, thus making the execution time will be faster.

Advanced Stored Procedure from SQL Server

Now execute the query, and it will execute with the updated values.

The table has been updated with the newly created values. Since I haven’t declared the data type for the test lead, the first value is used. I changed the salary for the aforementioned values using the convert function. So, the last values remained the same.

Conclusion

I trust the above explanation is understandable and clear enough to create from your side.

This tutorial briefly explains stored procedures, how to create a simple stored procedure, and discusses the advantages and recommended practices for writing stored procedures.

A stored procedure has many benefits for accessing the database. It will increase your application’s performance by reducing network traffic and helping store logic in the database.