How to Create stored procedure with input parameters Step By Step

Welcome to Appsloveworld, I have written this article to understand How to create a stored procedure with an input parameter in SQL server step by step.

In This post we discuss the following points

• What is Stored Procedure?
• A simple procedure example
• Creating a stored procedure with parameters.
• Altering stored procedure
• Execute stored procedure with a parameter
• Viewing the text of a stored procedure
• Execute stored procedure with multiple input parameters
• Advantages of using an SQL stored procedures

And finally, we’ll see how to drop stored procedures.

What is a stored procedures?

A stored procedure is a group of transacts SQL statements, If you ever have a situation where you have to write the same query over and over again, you can save that specific query as a stored procedure and call it just by its name.

Let’s understand what we mean by this with an example. Now I have this table called Company_Customer. which is got, the Id, CustomerName, CustomerCity, and CustomerSalary columns.

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

Let’s say I want CustomerName, CustomerCity, and CustomerSalary from the Company_Customer Table.So we have to write the below query

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

So every time I want the CustomerName, CustomerCity, and CustomerSalary of a Customer, I have to write this query, So instead of that, we can wrap this query inside a stored procedure and call that stored procedure so that you don’t need to write SQL query repeatedly.

How do we create a stored procedure?

To create a stored procedure We use create procedure command, create procedure and then you have to give procedure name.

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

Since above procedure returns the Customer detail I’m named it, spgetcustomer, and look at the name.

I have letters ‘sp‘, a common naming convention for procedures is that we usually prefix that with small letter ‘s’ and small letter ‘p’, indicating that, just by looking at the name, you can tell, this is a stored procedure.

And the definition of your stored procedure goes between this BEGIN and END Block.

Now if you want to look at the stored procedure that you have just created, you want to make sure that if this procedure is actually created or not,

1. Go into that database, and then you have a folder called Programmability.
2. Expand that and you should see a folder calls stored procedures
   

How to Execute Store procedure?

So if you want to execute, you just need the name of the procedure.

To execute the stored procedure. You just highlight that and click execute.

EXEC spGetcustomer;

You don’t have to write the query anymore. Now, you might be wondering it’s a very simple sql query, why we write a procedure for this simple query.

This example we have taken a very simple procedure. But In reality, the procedures can be up to 6000 lines of code that consist of very complex logic. And stored procedures have several other benefits also like security to the network, reducing network traffic, etc.

So we use “create procedure” or “create proc” statement to create procedures.
I mean, you can either say “create procedure” or you can just say “create proc” for the shortcut,

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

How to create a stored procedure with parameters?

Now, let us look at a simple example of how to create a stored procedure with parameters?.So let’s go back to the table, Company_Customer, now I want to create a stored procedure that takes one parameter, salary.

For example, if I pass a salary as 1000 in our procedure then it will give me all customers who’s salary is greater then 1000.so our stored procedure needs to have this parameter. just like 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 whole procedure is expecting the @Salary parameter. If I don’t pass the parameter and if I try to execute that stored procedure, then what’s going to 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 makes sense. It’s expecting a Salary parameter which is not supplied. So we need to pass in  @Salary parameter.

If i want the all customer with a salary greater than 3000, .

EXECUTE spGetcustomerbySalary 3000

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

How Execute stored procedure with multiple input parameters

Let Say I want to create a store procedure in which salary and name as input parameter and procedure then give me all customers whose salary is equal to @salary and the name is equal to the pass in @name parameters. so our stored procedure needs to have input multiple 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

Let’s say I have created two procedures until spgetcustomer and spGetcustomerbySalary.

Now, if I want to view the text of these two procedures, what are the different ways that are available?.

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 look at the definition of a stored procedure. And the other way is to use a system store procedure.

The above-stored procedures that we have created are user-defined, stored procedures. These are not systems define procedures.

SQL Server also has some system-defined stored procedures. And we use it for many tasks. For example, we want to find the text of the stored procedure. How do we do that? we can use a system procedure called sp_helptext.

if I pass in the name of the stored procedure in spgetcustomer.And then when I select them together and execute , I get the text of my store procedure.

sp_helptext spgetcustomer

whenever we name user-defined, stored procedure, Microsoft recommends not to use sp_ prefix for user-defined store procedures, because system stored procedures have that prefix.

• Now, if you happen to use sp_ prefix for your user-defined stored procedures, there are two problems. Number one, there will be an ambiguity between user-defined, stored procedures, and system-defined stored procedures.
• Just by looking at the name, we cannot tell, is this a user-define stored procedure or system-defined, stored procedure.
• And another problem is with future releases of the new SQL server version, there may be named conflicts. To avoid problems like this, it’s always better not to prefix user-defined, stored procedures with sp_ prefix.

Advantages of using an SQL stored procedures

1. Capable of reusing the execution plan

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

Okay, let’s understand what you mean by an execution plan. First of all when we issue a query to SQL Server three things happen.
1. It checks the syntax of the query
2. It compiles that query
3. It generates an execution plan.

what do we mean by an execution plan?

An execution plan is a query to retrieve the data, the best possible way available to execute that query.  it depends on the indexes that are available in SQL Server to help that query.

So based on all these factors, it’s going to generate an execution plan and then execute the query.

So next time when you issue the same query since that execution plan is already generated. It’ll be cashed by SQL Server. So next time when you run the same query what’s going to happen? It’s going to reuse that execution plan.

Stored procedures have been doing this for a long time. they are capable of retaining the execution plan, caching that execution plan, and reusing that.

Now even with the ad hoc queries these days, they are able to reuse the execution plan with the new versions of SQL Server that we have.

But the problem is even a slight change in the query if it’s an ad hoc query that can prevent the execution plan from being reused.

Let’s understand what we mean by that. I have the table call Users, which is very simple. if you look at the query below

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

when I execute this query what’s going to happen the first time when we issue this query, the syntax is checked. It is compiled and an execution plan is generated. Then it executes this query returns the data back, which is very good.

Now when we execute you this query again, then reuse execution plan and it would be quick.

But on the other hand, if I change the parameter, if I pass ID is equal to 1005 it is not going to reuse the execution plan is going to generate a new plan for this.

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

but whereas it comes to stored procedure it reuses the execution plan. Look at this, I have the 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 gives the Id of the user.

So now when I execute this one,

spGetUserNameById 1003

Look at this parameter that I’m passing this 1003. So basically the same thing happens, the procedure is compiled, and then an execution plan is generated, etc. And then when I execute this again, the execution plan is reused.

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

2. Reduce the Network traffic

Another benefit stored procedure is that they can reduce Network traffic how can they reduce the Network traffic? Let’s understand that.

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

There is a body for the stored procedure and within the body of the stored procedure, they have all logic, I have seen stored procedures with 4000 lines of code within them lots of logic happening.

Now when we have such a kind of stored procedure and we want to execute that stored procedure.

All you have to do is from the client application. It could be a .Net application, Java application, or simply a SQL Server management studio.

We just need to specify the name of the procedure and the parameter values and then we can execute that.

spGetUserNameById 1003

So over the network what gets transmitted just three words(.ie spGetUserNameById 1003).

But if you don’t have a stored procedure if it is just ad hoc SQL query that you want to send from the client tools.

Then you have to send all those four thousand lines of code, which is definitely going to have an impact on network traffic.

3.Another advantage of using stored procedures is code  reusability and better maintainability

Store procedure resides on the server ,and several applications can re-use that store procedure. If multiple applications want a similar kind of data they can just call the stored procedure.

Now, what is the advantage of doing so?

The advantage of doing so is that tomorrow for some reason if there is a bug in the stored procedure or the logic of the stored procedure has to be changed you only have one place to change it and irrespective of that being used by multiple applications.

So maintainability also becomes much easier

The post How to Create stored procedure with input parameters Step By Step appeared first on Software Development | Programming Tutorials.