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
- Which SQL operation will give me the product of two tuples?
- Add custom header with sql server database mail
- SQL SUM giving 2 rows. I want 1 row
- Primary key indexes
- How to delete completely duplicate rows
- Using logical functions on grouping in T-SQL
- ftrows_FTP...ndf file preventing a restore
- How to find slowest queries
- Optimizing JOIN with UNION is not working
- why temporary tables are better in performance then the normal table even though they are stored in different db i.e; tempdb
- Measuring Query Performance : "Execution Plan Query Cost" vs "Time Taken"
- How can i solve a "Model compatibility cannot be checked because the database does not contain model metadata" error?
- Case statement with multiple whens...not working!
- How can I view I/O stats of a partitioned table at the partition level
- Concatenate SQL table rows with conditions in single text
- How do you use RODBC with a Query containing DDL Commands?
- why varbinary instead of varchar
- Split Rows in multiple rows
- How to select multiple columns and group by one column
- SQL DISTINCT on multiple columns except one, combine last column in result
- Drop all objects in SQL DB?
- T-SQL User Defined Function or similar to calculate Max Value (Date) from a list of values
- How to add quotes into sql where clause in Groovy script?
- Extracting Username\password from text\properties file in VBScript
- SQL Server Scripts 2012 Project into Team Foundation Server 2012
- Get id from stored procedure insert with Dapper in .NET Core
- SQL distribute values across rows
- SQL show closest available date within 30 days from a range of dates
- Sorting all data by date with Group By
- SQL Indexing - Computed Column vs Field Used by Computed Column
- Count the amount of years from a certain date SQL
- how to find particular Employee total amount
- How to find UserDefined Objects that reference System tables/functions in SQL Server
- Combining rows with NULL columns
- Using Scoring to find Best Match in SQL
- how to separate string into different columns?
- What's the best place to run a reccuring task for a website you host yourself?
- SQL: Order Month_year in date order
- Copy table to a different database on a different SQL Server
- Connecting to a remote database using Analysis Services
- Unexpected result from CTE on cyclic data
- LINQ Join Tables from Different Database or DataContext
- GETDATE() method for DB2
- Create a simple select funtion in SQL Server
- c# 2008 SQL Server Express Connection String
- Insert string after 4 results
- Categories all dates by month in SQL Server with set-based approach
- How to calculate overlaping time and length of conflict
- How can I easily flatten this Sql Server hierarchy into an inherited inclusive list?
- determine if geography point falls on geography polygon line
- SQL convert datetime to varchar
- How to concatenate strings and commas in SQL Server?
- Connecting OLAP Cube through JQuery.ajax() – XML/A
- Apply filtering criteria to several SQL columns based on the values of temporary table
- Which columns to index
- Dynamic WHERE clause
- SQL Joins for Multiple Fields with Null Values
- Trouble using stored procedure to insert query results into table
- SSIS Renaming a file with file extension appearing in the correct place
- Are all available DTU used to exec a query?
- If a table has 'id' column as it's clustered index, is there any benefit in adding 'id' column as an included column in any other non-clustered index
- After restart of host for single node SQL-BDC cluster endpoints are unavailable
- How can I fix this Access 2003 bug? Data Entry Auto-Generating a Value
- iphone image to Ruby on Rails - bad content body, parse_multipart
- Incorrect syntax when working with a variable in IDENTITY
- Get the Count of Concatenating Column in sql
- how to group corresponding values in 2 columns in SQL?
- SQL user login access to views only
- T-SQL Consolidate and merge two rows into one
- SQL Server Browser showing only hostname, not instance name
- Does the number of columns used for a CTE affects the performance of the query?
- The multi-part identifier could not be found
- How to get minimum fare between two cities
- How to find if a string starts with a number and a bracket in SQL server?
- Modifying SQL Server Schema Collection
- Remove Alphabetic Characters in SQL Server
- Fastest way to fetch huge amount of data from SQL Server database
- Select empty weeks in SQL
- SQL SELECT statement (in vb.net) returning empty and/or weird rows
- SQL Server: inline conditional convert with XML?
- Pandas to_sql changing datatype in database table
- How to increase hangfire job timeout
- Use of SqlParameter in SQL LIKE clause not working
- SQL Server stopped using INDEX after COLLATION change
- Ignoring specific letters to find match in SQL Query
- Using variables inline SQL ( MSSQL )
- SQL Server 2008 data types: which ones should i use?
- SQL query issue with zero discount
- Create new SQLCommand's or reuse the same one
- Should I still see the query hit in SQL Profiler?
- Issue to insert record with datetime to SQL Server 2014 via ODBC C++
- Seperate Overlapping Date Records with Priority
- SELECT BETWEEN CHAR Times
- How to round a number to 2nd decimal from a SQL query returned to textbox
- SQL statement to check for empty string - T-SQL
- SQL Server: IsMSShipped = 0 but system SPs still returned
- Converting date to proper format
- What is the benefit of caching an IReader?
- how to create a pivot query using the same column
- How to use .NET Core secrets in .sh file that is called from docker-compose
- Serialize JSON Before Inserting into Database
- Apply like over all columns without specifying all column names?
- ODBC Driver 11 for SQL Server and SQLGetData limitations
- SQL Server Compact: Left Join returns only one row FOR MOST entries
- Insert local data into SQL Server temp table
- How do I update a given SQL row in C# when it has no known or guaranteed unique ID?
- How do I title columns as a variable in SQL?
- Loops within dynamic SQL
- Exercise and database for sql server
- Datetime type mismatch trying to publish a SQL CLR database
- Filter the data from the SQL Query
- Find tables name in multi-part identifier
- How to select next non-null value In each column. T-SQL
- I am trying to write a query to track use of terminals. I want to show all terminals that have not had activity for 1 year
- SQL How to compare if a variable is the same as a value in a database column?
- Pull Manager Name From LDAP Query [ SQL View Created from LDAP Query ]
- How to subset the readmitted cases from an inpatients’ table to calculate the total length of stay of the readmitted cases in SQL Server 17?
- How to connect with WinAuth to SQL Server without desktop login?
- SQL Group By Cube and get total of each combination
- SQL Select Master Records and Display Number of Detail Records for Each
- How to extract URL querystring parameters in SQL Server without writing a function?
- Complex SQL Server Query between more than one database
- Query to list each child record in columns of a parent
- T-SQL: Why use INSERT INTO tblA SELECT .. FROM (MERGE tblA USING tblB ..)?
- Convert from LocalTime to TimeSpan
- Stored procedure for auto generating roll no?
- Read from XML field and write to another XML field sql server
- Looping through a stored Procedure with different values
- Why (and how) to split column using master..spt_values?
- Validating Type 2 Slowly Changing Dimensions table
- How to Select all Items and Their Most Recent Values
- How do I set up SQL Server LocalDB to run on a client's machine?
- Connecting Grails to MS SQL using trusted Connection
- unusual sql server query result
- Is it possible to specify condition in Month()?
- Using CASE Statement inside IN Clause
- Copy one table data to another database table in same server Azure Services
- Restore selected tables from .bak file
- Start stored procedures sequentially or in parallel
- What is the best way to store a date without a year in SQL database?
- how to encode int with base32 in sql server 2008
- Data mismatch when querying with different indexes
- SQL - Correctly format CSS class using XML PATH
- Displaying table containing dates and other records
- Unable to delete zip files from DB server location using xp_cmdshell
- SQL 2008 Linked Server returns different results than local query
- Executing sql files automatically
- Order the data as the IDs are ordered in the given list
- Appropriate .Net data type for decimal(20,0)
- Getting the sum of quantity by product id