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,
- Go into that database, and then you have a folder called Programmability.
- 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.
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?.
- One way is to simply right-click on that stored procedure
- 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.
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,
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.
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.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?