Create A Stored Procedure In SQL Server

In this article you will learn about how to create a stored procedure in SQL without parameter and with parameter .This article  is useful for beginner who want to learn how to create SP.

What is a Stored Procedure?

A SQL stored procedure (SP) is a collection SQL statements and sql command logic, which is compiled and stored on the database. Stored procedues in SQL allows us to create SQL queries to be stored and executed on the server. Stored procedures can also be cached and reused. The main purpose of stored procedures to hide direct SQL queries from the code and improve performance of database operations such as select, update, and delete data.

You can create and execute stored procedures using the Object Explorer in SQL Server or using SQL Server Management Studio (SSMS).

So let's start with how to create a stored procedure without parameter.

1. CREATE PROCEDURE stpGetAllUserDetail  
2. AS  
3. BEGIN   
4.     -- Select statements for procedure here  
5.     Select * from tblUserDetail  
6. END  

What are parameters in stored procedures?

Parameters in SPs are used to pass input values and return output values. There are two types of parameters:

1. Input parameters - Pass values to a stored procedure.
2. Output parameters - Return values from a stored procedure.

How to create a SELECT query SP with parameters?

In the previous steps, we created a simple SP that returned all rows from a table. Now, let's create a new SP that will take a city name as an inpurt parameter and will return all rows where city name matches the input parameter value.

Here is the updated SP with a parameter @CityName.

1. GO  
2. -- =============================================  
3. -- Author:      Arjun Walmiki  
4. -- Create date: 19-April-2020  
5. -- Description: Return specific city records  
6. -- =============================================  

1. CREATE PROCEDURE stpGetAllUserDetailByCityName  
2.  -- Add the parameters for the stored procedure here  
3.     @CityName nvarchar(30)  
4. AS  
5. BEGIN   
6.     
7. -- SET NOCOUNT ON added to prevent extra result sets from  
8.     -- interfering with SELECT statements.  
9.     SET NOCOUNT ON;  
10.   
11.     Select * From tblUserDetail  
12.     where City like '%'+@CityName+'%'  
13.  
14. END  
15. GO

How to create a INSERT query based stored procedure?

 

We can use an INSERT INTO SQL query to insert data into a table. The following SQL statement creates an INSERT SP with three parameters.

1. SET ANSI_NULLS ON  
2. GO  
3. SET QUOTED_IDENTIFIER ON  
4. GO  
5. -- =============================================  
6. -- Author:      Arjun Walmiki 
7. -- Create date: 19-April-2020  
8. -- Description: To create a new member  
9. -- =============================================  
10. CREATE PROCEDURE stpInsertUserDetail 
11. @Name varchar(50),  
12. @City varchar(25),  
13. @Phone varchar(15)  
14.   
15. AS  
16. BEGIN  
17.     -- SET NOCOUNT ON added to prevent extra result sets from  
18.     -- interfering with SELECT statements.  
19.     SET NOCOUNT ON;  
20.   
21.     Insert into tblUserDetail (Name,City,Phone)   
22.            Values (@Name,@City, @Phone)  
23.   
24. END  
25. GO  

Thank you Hope it help to you !!!!

Arjun Walmiki