How to Create Stored Procedure with input parameters Step By Step

Welcome to Quickpickdeal! I've written this article to guide you through creating a stored procedure with an input parameter in SQL Server, step by step.

In this post, we'll cover the following points:

• Creating a stored procedure with parameters
• Altering a stored procedure
• Executing a stored procedure with a parameter
• Viewing the text of a stored procedure
• Executing a stored procedure with multiple input parameters
• Advantages of using SQL stored procedures
• Finally, we'll see how to drop stored procedures."

What is a stored procedures?

A stored procedure is a group of Transact-SQL statements. If you find yourself in a situation where you have to write the same query repeatedly, you can save that specific query as a stored procedure and call it simply by its name.

Let's illustrate this with an example. Consider a table called Company_Customer, which contains columns such as Id, CustomerName, CustomerCity, and CustomerSalary.

Sql Table Script:

CREATE TABLE [dbo].[Company_Customer](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [nvarchar](150) NOT NULL,
    [CustomerCity] [nvarchar](500) NOT NULL,
    [CustomerSalary] [decimal](18, 2) NOT NULL,
 
) 

Insert Script

SET IDENTITY_INSERT [dbo].[Company_Customer] ON 
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (4, 'John', 'California', CAST(1000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (5, 'Marry', 'Washington dc', CAST(2000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (1012, 'Ashok', 'New Delhi', CAST(2000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (1014, 'John', 'California', CAST(1000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (1015, 'Marry', 'Washington dc', CAST(2000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (1016, 'Pankaj Patel', 'Kanpur', CAST(5000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (1019, 'Neeraj Singh', 'Banglore', CAST(6000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (1020, 'Aakankshi Gupta', 'London', CAST(40000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (1022, 'Ashish Thapliyal', 'Paris', CAST(5000.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[Company_Customer] ([Id], [CustomerName], [CustomerCity], [CustomerSalary]) VALUES (1023, 'Marry', 'Washington dc', CAST(2000.00 AS Decimal(18, 2)))
GO
SET IDENTITY_INSERT [dbo].[Company_Customer] OFF
GO

Suppose I want to retrieve CustomerName, CustomerCity, and CustomerSalary from the Company_Customer table. In that case, we would need to write the following query:

select CustomerName,CustomerCity,CustomerSalary from [dbo].[Company_Customer]

So every time I need to retrieve the CustomerName, CustomerCity, and CustomerSalary of a customer, I have to write this query. Instead of repeating the query, we can encapsulate it inside a stored procedure and then call that stored procedure whenever needed, saving us from writing the SQL query repeatedly.

How do we create a stored procedure?

To create a stored procedure, we use the "CREATE PROCEDURE" command, followed by the procedure name.

CREATE PROCEDURE spgetcustomer
AS
BEGIN
select CustomerName,CustomerCity,CustomerSalary from [dbo].[Company_Customer]
END

Since the above procedure returns customer details, I've named it spgetcustomer, and if you notice the name, it includes the letters 'sp'. A common naming convention for procedures is to prefix it with 'sp', indicating that it's a stored procedure.

The definition of the stored procedure is enclosed between the BEGIN and END blocks.

To verify whether the stored procedure has been created, you can navigate to the database and locate the folder called "Programmability". Expand that folder, and you should see another folder named "Stored Procedures".

How to Execute Store procedure?

To execute the stored procedure, you only need its name. Simply highlight the procedure name and click on "Execute".

EXEC spGetcustomer;

You no longer need to write the query manually. Now, you might wonder why we would create a procedure for such a simple query.

In this example, we've demonstrated a very basic procedure. However, in reality, procedures can span up to 6000 lines of code, containing complex logic. Stored procedures offer numerous benefits, such as enhancing network security and reducing network traffic.

To create procedures, we use the "CREATE PROCEDURE" or "CREATE PROC" statement. In other words, you can use either "CREATE PROCEDURE" or its shorthand, "CREATE PROC" for brevity.

CREATE PROC spgetcustomerNew
AS
BEGIN
select CustomerName,CustomerCity,CustomerSalary from [dbo].[Company_Customer]
END

How to create a stored procedure with parameters?

Now, let's examine a simple example of how to create a stored procedure with parameters. Let's revisit the table, Company_Customer. I want to create a stored procedure that accepts one parameter, salary.

For instance, if I pass a salary of 1000 to our procedure, it should return all customers whose salary is greater than 1000. Therefore, our stored procedure needs to have this parameter, similar to how functions have parameters in C#.

 So let’s see how to do that?

Create Procedure spGetcustomerbySalary
@Salary decimal
as
Begin
Select CustomerName,CustomerCity,CustomerSalary from [dbo].[Company_Customer] where CustomerSalary>@Salary
End

Now, this entire procedure expects the @Salary parameter. If I attempt to execute this stored procedure without passing the parameter, what will happen?

“Msg 201, Level 16, State 4, Procedure spGetcustomerbySalary, Line 0 [Batch Start Line 11]
Procedure or function ‘spGetcustomerbySalary’ expects parameter ‘@Salary’, which was not supplied.”

And that's understandable. The procedure expects a Salary parameter, which hasn't been provided. Therefore, we need to pass in the @Salary parameter.

For example, if I want to retrieve all customers with a salary greater than 3000...

EXECUTE spGetcustomerbySalary 3000

So we have seen how to create a simple stored procedure, and we have also learned how to create a procedure with parameters. Additionally, we have learned how to execute them as well.

How Execute stored procedure with multiple input parameters

Let's say I want to create a stored procedure with input parameters for salary and name. The procedure should then return all customers whose salary is equal to the @salary parameter and whose name matches the @name parameter passed in. Therefore, our stored procedure needs to have multiple input parameters.

Create Procedure spGetcustomerbyCityandName
@Salary decimal,
@Name varchar(100)
as
Begin
Select CustomerName,CustomerCity,CustomerSalary from [dbo].[Company_Customer] where CustomerSalary=@Salary and CustomerName=@Name
End

Now let’s execute the above store procedure

EXECUTE spGetcustomerbyCityandName 1000,'John'

It will return all customers with salary=1000 and name=’John’

Viewing the text of a stored procedure

Suppose I have created two procedures named spgetcustomer and spGetcustomerbySalary. Now, if I want to view the text of these two procedures, what are the different available methods?

1. One way is to simply right-click on that stored procedure
2. Script stored procedure as ->create to ->new creator window.

This generates the contents of that store procedure. Look at this.

This is one way to view the definition of a stored procedure. Another way is to use a system stored procedure.

The stored procedures we have created above are user-defined stored procedures, not system-defined procedures.

SQL Server also provides some system-defined stored procedures, which are used for various tasks. For example, if we want to find the text of a stored procedure, we can use a system procedure called sp_helptext.

By passing in the name of the stored procedure to sp_helptext and then executing it, we can retrieve the text of our stored procedure.

sp_helptext spgetcustomer

When naming user-defined stored procedures, Microsoft recommends avoiding the use of the 'sp_' prefix because system stored procedures already have that prefix.

Using the 'sp_' prefix for user-defined stored procedures can lead to two problems. Firstly, there may be ambiguity between user-defined and system-defined stored procedures. Simply by looking at the name, it may not be clear whether it is a user-defined or system-defined stored procedure.

Secondly, there may be conflicts with future releases of new SQL Server versions. To prevent such problems, it's advisable not to prefix user-defined stored procedures with 'sp_'.

Advantages of using an SQL stored procedures

1. Capable of reusing the execution plan

The greatest benefit of using stored procedures is that they are capable of reusing the execution plan.

Let's understand what is meant by an execution plan. When we issue a query to SQL Server, three things happen:

• It checks the syntax of the query.
•  It compiles the query.
• It generates an execution plan.

What do we mean by an execution plan?

An execution plan represents the best possible way available to execute a query to retrieve data. It depends on the indexes available in SQL Server to optimize that query. Based on these factors, SQL Server generates an execution plan and then executes the query.

The advantage of having an execution plan is that it gets cached by SQL Server. So, the next time you run the same query, SQL Server can reuse that execution plan, which enhances performance.

Stored procedures have been capable of retaining and reusing execution plans for a long time. Even with ad hoc queries, newer versions of SQL Server can reuse execution plans. However, even a minor change in an ad hoc query can prevent the execution plan from being reused.

Let's consider a simple example with a table named Users. If you look at the query below:

select Name from [dbo].[Users] where Id=1003

When I execute this query for the first time, several steps occur: the syntax is checked, the query is compiled, and an execution plan is generated. Then, the query is executed, and the data is returned, which is efficient.

Now, when I execute the query again, SQL Server reuses the execution plan, resulting in quick execution.

However, if I change the parameter, for example, if I pass ID equal to 1005, SQL Server will not reuse the execution plan. Instead, it will generate a new plan for this query.

select Name from [dbo].[Users] where Id=1005

But when it comes to stored procedures, they reuse the execution plan. Take a look at this stored procedure:

Create Procedure [dbo].[spGetUserNameById]
@Id int
as
Begin
Select Name from Users Where Id = @Id
End

So when you pass the parameter to the stored procedure, it retrieves the Id of the user.

Now, when I execute this:

spGetUserNameById 1003

Notice the parameter I'm passing, which is 1003. Essentially, the same process occurs: the procedure is compiled, and then an execution plan is generated. Subsequently, when I execute it again, the execution plan is reused.

Even if I change the parameter to 1005, in the case of stored procedures, the same plan will be reused. However, that's not the case with ad hoc SQL queries.

2. Reduce the Network traffic

Another benefit of stored procedures is that they can reduce network traffic. How can they achieve this? Let's delve into that.

A stored procedure. Usually something like this.
Create Procedure [dbo].[spGetUserNameById]
@Id int
as
Begin
Select Name from Users Where Id = @Id
End

A stored procedure typically contains a body where all the logic resides. I've seen stored procedures with up to 4000 lines of code, encompassing various complex operations.

Now, when we want to execute such a stored procedure, all we need to do is specify its name and the parameter values from the client application. This could be a .NET application, a Java application, or simply SQL Server Management Studio. Once specified, we can execute the stored procedure.

spGetUserNameById 1003

So over the network, only three words are transmitted (.i.e., spGetUserNameById 1003).

However, if you don't have a stored procedure and instead use an ad hoc SQL query that you want to send from the client tools, you would have to send all those four thousand lines of code. This would certainly have a significant impact on network traffic.

3.Using stored procedures offers code reusability and better maintainability

Stored procedures reside on the server, allowing multiple applications to reuse them. If several applications require similar data, they can simply call the stored procedure.

What are the advantages of this approach?

The advantage is that if, for some reason, there is a bug in the stored procedure or if the logic of the stored procedure needs to be changed, you only need to make the change in one place. This is regardless of the fact that the stored procedure is being used by multiple applications. As a result, maintainability becomes much easier.