How To Create a Stored Procedure In SQL Server Management Studio (SSMS)

Introduction

In this article, you will learn about how to create a stored procedure in SQL. This article covers answers to the following questions,

What is a stored procedure in SQL?
Why do we use SET NOCOUNT ON in a stored procedure?
How many types of stored procedures are there?
How to write comments in SQL Server?
What are the naming conventions for stored procedures?
How to create a stored procedure to select data from a database table using SELECT SQL query?
How to execute stored procedures in SQL Server?
What are the parameters in stored procedures?
How to create parameters in a SELECT query stored procedure which returns records as per the parameter passed?
How to create an INSERT query-based stored procedure?
How to create an UPDATE query-based stored procedure?
How to create a stored procedure to delete records using the DELETE query?

What is a Stored Procedure?

A SQL stored procedure (SP) is a collection of SQL statements and SQL command logic, which is compiled and stored in the database. Stored procedures in SQL allow 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 is to hide direct SQL queries from the code and improve the performance of database operations such as select, update, and delete data. Here is a detailed article on Stored Procedures in SQL.

You can create and execute stored procedures using the Object Explorer in SQL Server or using SQL Server Management Studio (SSMS). If you're new to SSMS, try SQL Server Management Studio.

Why do we use SET NOCOUNT ON in a stored procedure?

While we set SET NOCOUNT ON it means there are no messages which show the number of rows affected.

NOCOUNT means do not count that is ON.

Now you will come to know what happened when SET NOCOUNT OFF.

Types of Stored Procedures in SQL Server

There are two types of stored procedures available in SQL Server.

User-defined stored procedures
System stored procedures

User-defined stored procedures

User-defined stored procedures are created by database developers or database administrators. These SPs contain one more SQL statements to select, update, or delete records from database tables. User-defined stored procedures can take input parameters and return output parameters. A user-defined stored procedure is a mixture of DDL (Data Definition Language) and DML (Data Manipulation Language ) commands.

User defined SPs are further classified into two types

T-SQL stored procedures- T-SQL (Transact SQL) SPs receive and return parameters. These SPs process the Insert, Update and Delete queries with or without parameters and return data of rows as output. This is one of the most common ways to write SPs in SQL Server.
CLR stored procedures- CLR (Common Language Runtime) SPs are written in a CLR-based programming language such as C# or VB.NET and are executed by the .NET Framework.

System stored procedures

System-stored procedures are created and executed by SQL Server for the server's administrative activities. Developers usually don't interfere with system SPs.

Login to SQL Server database

Let's log in to our SQL Server database, so we can achieve the following:

How to create a SELECT QUERY-based stored procedure which returns all records?
How to create a PARAMETER-based SELECT QUERY stored procedure which returns records based on parameters?
How to create an INSERT query-based stored procedure?
How to create an UPDATE query-based stored procedure?
How to create a DELETE query-based stored procedure?

stored procedure In SQL Server

Switch to your database. My database name is MBKTest.

stored procedure In SQL Server

AN Empty stored procedure will be created using the following:

stored procedure In SQL Server

The empty template created by SQL Server for an SP looks like the following. The CREATE PROCEDURE SQL command is used to create a procedure, followed by an SP name and its parameters. The BEGIN and END area is used to define the query for the operation. This is where you will write select, update, insert, or delete queries.

-- ================================================  
-- Template generated from Template Explorer using:  
-- Create Procedure (New Menu).SQL  
--  
-- Use the Specify Values for Template Parameters   
-- command (Ctrl-Shift-M) to fill in the parameter   
-- values below.  
--  
-- This block of comments will not be included in  
-- the definition of the procedure.  
-- ================================================  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
-- =============================================  
-- Author:      <Author,,Name>  
-- Create date: <Create Date,,>  
-- Description: <Description,,>  
-- =============================================  
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>   
    -- Add the parameters for the stored procedure here  
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,   
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>  
AS  
BEGIN  
    -- SET NOCOUNT ON added to prevent extra result sets from  
    -- interfering with SELECT statements.  
    SET NOCOUNT ON;  
  
    -- Insert statements for procedure here  
    SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>  
END  
GO

How to write comments in SQL SERVER?

You can comment in the SQL server in the following ways,

-- (two hyphens / dash) for a single line of comment.
start with /* ……. end with  */    for multiline comments.

What is the naming convention for stored procedures?

We must follow standard naming conventions which may also depend on your project and coding policies.

For user-defined stored procedure naming conventions, my suggestions are to add one of the following prefixes to your SP names.

sp
stp
stp_
udstp
udstp_

Naming conventions are just to identify objects. By adding these prefixes to the name, we can clearly identify that this object is a stored procedure.

Create a database table

Before we can create and execute any SPs, we need a database table. I create a database table named, “tblMembers” using the following SQL query and execute it on the server. As you can see, my table has 4 columns where the first column is an identity column. Once the table is created, open table in your SSMS and add some data by manually entering data into the table.

USE [MBKTest]  
GO  
  
/****** Object:  Table [dbo].[tblMembers]    Script Date: 18-Nov-17,Sat 6:47:55 PM ******/  
SET ANSI_NULLS ON  
GO  
  
SET QUOTED_IDENTIFIER ON  
GO  
  
SET ANSI_PADDING ON  
GO  
  
CREATE TABLE [dbo].[tblMembers](  
    [MemberID] [int] IDENTITY(1,1) NOT NULL,  
    [MemberName] [varchar](50) NULL,  
    [MemberCity] [varchar](25) NULL,  
    [MemberPhone] [varchar](15) NULL  
)  
  
GO  
  
SET ANSI_PADDING OFF  
GO

How to create a SELECT stored procedure?

Click on your Database and expand the “Programmability” item and right-click on “Stored Procedures” or press CTRL + N to get a new query window. In the query area between BEGIN and END, type your SELECT statement to select records from the table. See the Select statement in the below code.

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
-- =============================================  
-- Author:      Manoj Kalla  
-- Create date: 18th Nov 2017  
-- Description: Return all members  
-- =============================================  
--Store procedure name is --> stpGetAllMembers  
CREATE PROCEDURE stpGetAllMembers  
AS  
BEGIN  
    -- SET NOCOUNT ON added to prevent extra result sets from  
    -- interfering with SELECT statements.  
    SET NOCOUNT ON;  
  
    -- Select statements for procedure here  
    Select * from tblMembers  
END  
GO

Now, press F5 or click on Execute button to execute the SP.

stored procedure In SQL Server

You should see a message, “Command(s) completed successfully.”

Now go to Programmability -->Stored Procedures, Right Click, and select Refresh.

You can see in the following image, the new SP called stpGetAllMembers is created.

stored procedure In SQL Server

Execute stored procedures in SQL Server

In the below UI, right-click on the SP name and select Execute Stored Procedure... to execute a SP. From here, you can also modify an exisiting SP.

stored procedure In SQL Server

Alternatively, you can also execute an SP from the Query window.

To run the stored procedure in SQL Server Management Studio, switch to the Query window or CTRL +N to open a new query window and type the following command.

Syntax - EXEC <stored procedure name>
Example - EXEC stpGetAllMembers

Now, we run our stored procedure called stpGetAllMembers. The output looks like the following:

OUTPUT

stored procedure In SQL Server

What are the parameters in stored procedures?

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

Input parameters - Pass values to a stored procedure.
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 input parameter and will return all rows where the city name matches the input parameter value.

Here is the updated SP with a parameter @CityName.

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
-- =============================================  
-- Author:      Manoj Kalla  
-- Create date: 20-Nov-2017  
-- Description: Return specifc city records  
-- =============================================  
CREATE PROCEDURE stpGetMembersByCityName  
    -- Add the parameters for the stored procedure here  
    @CityName nvarchar(30)  
      
AS  
BEGIN  
    -- SET NOCOUNT ON added to prevent extra result sets from  
    -- interfering with SELECT statements.  
    SET NOCOUNT ON;  
  
    Select * From tblMembers   
    where MemberCity like '%'+@CityName+'%'  
          
END  
GO

Execute it.

To run this SP, type the following command in the SQL query tool:

EXEC GetMemberByCityName @CityName = 'mal'

OR from the UI, run the SP and provide the following input.

stored procedure In SQL Server

The code to execute looks like the following:

USE [MBKTest]  
GO  
  
DECLARE @return_value int  
  
EXEC    @return_value = [dbo].[GetMemberByCityName]  
        @CityName = N'mal'  
  
SELECT  'Return Value' = @return_value  
  
GO

OUTPUT

stored procedure In SQL Server

How to create an 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.

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
-- =============================================  
-- Author:      Manoj Kalla  
-- Create date: 20-Nov-2047  
-- Description: To create a new member  
-- =============================================  
CREATE PROCEDURE stpInsertMember  
@MemberName varchar(50),  
@MemberCity varchar(25),  
@MemberPhone varchar(15)  
  
AS  
BEGIN  
    -- SET NOCOUNT ON added to prevent extra result sets from  
    -- interfering with SELECT statements.  
    SET NOCOUNT ON;  
  
    Insert into tblMembers (MemberName,MemberCity,MemberPhone)   
           Values (@MemberName,@MemberCity, @MemberPhone)  
  
END  
GO

Right-click on the stored procedure in Object Explorer and select Refresh.

stored procedure In SQL Server

Pass the value of the parameter in Execute dialog box. Something like this:

stored procedure In SQL Server

The following code can be used to execute this SP in SSMS.

USE [MBKTest]  
GO  
  
DECLARE @return_value int  
  
EXEC    @return_value = [dbo].[stpInsertMember]  
        @MemberName = N'Mahesh Chand',  
        @MemberCity = N'NewYork',  
        @MemberPhone = N'9999945121'  
SELECT  'Return Value' = @return_value  
GO

OUTPUT

In the query window, you can check if a new record for Member Name 'Mahesh Chand' is added to the table.

stored procedure In SQL Server

You can also run the same SP in the code.

EXEC stpInsertMember @MemberName = 'Suhana & Ashish Kalla ', @MemberCity = 'Mumbai ', @MemberPhone = N'9022592774xxx'

stored procedure In SQL Server

OUTPUT

You can check “Suhana & Ashish Kalla” record is added successfully.

stored procedure In SQL Server

How to create an UPDATE query based stored procedure?

Let's create a new SP that will update a table record based on the Member ID column. The ID is passed as an input parameter. Here is the new SP that uses an UPDATE..SET..WHERE command.

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
-- =============================================  
-- Author:      Manoj Kalla  
-- Create date: 20-Nov-2017  
-- Description: Update a member detail by ID  
-- =============================================  
CREATE PROCEDURE stpUpdateMemberByID  
@MemberID int,  
@MemberName varchar(50),  
@MemberCity varchar(25),  
@MemberPhone varchar(15)  
  
AS  
BEGIN  
    -- SET NOCOUNT ON added to prevent extra result sets from  
    -- interfering with SELECT statements.  
    SET NOCOUNT ON;  
  
    UPDATE tblMembers  
    Set MemberName = @MemberName,  
        MemberCity = @MemberCity,  
        MemberPhone = @MemberPhone  
    Where MemberID = @MemberID  
END  
GO

Right-click on the stored procedure in the Object Explorer and select Refresh. You will see the SP is created.

Now, Right click on the SP name and select Execute stored procedure…. Provide the input values and execute.

stored procedure In SQL Server

We can use the following command in SSMS.

USE [MBKTest]  
GO  
  
DECLARE @return_value int  
  
EXEC    @return_value = [dbo].[stpUpdateMemberByID]  
        @MemberID = 20,  
        @MemberName = N'Nirupama Kalla',  
        @MemberCity = N'Mumbai',  
        @MemberPhone = N'904512541xxxx'  
  
SELECT  'Return Value' = @return_value  
  
GO

EXEC stpUpdateMemberByID 17,'Gopal Madhavrai','Bikaner','90454564xxx'

The results should show you the updated values.

stored procedure In SQL Server

How to create a DELETE query-based stored procedure?

Let's create an SP that will delete records. The new SP uses a DELETE command and deletes all records that match provided Member ID.

SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
-- =============================================  
-- Author:      Manoj Kalla  
-- Create date: 21-Nov-2017  
-- Description: Delete a Member by Member ID  
-- =============================================  
CREATE PROCEDURE stpDeleteMemberByMemberID   
    @MemberID int  
AS  
BEGIN  
    -- SET NOCOUNT ON added to prevent extra result sets from  
    -- interfering with SELECT statements.  
    SET NOCOUNT ON;  
  
    Delete from tblMembers  
    where MemberId = @MemberID  
      
END  
GO

Execute it.

Right-click on Stored Procedures in the Object Explorer and select Refresh.

RUN stored procedure BY UI

Now again right click on stored procedure and select Execute stored procedure…

As you can see in the image, I passed the @MemberID parameter value = 4.

stored procedure In SQL Server

RUN DELETE stored procedure BY MANUALLY (CODING)

EXEC stpDeleteMemberByMemberID 2

OUTPUT

You can see in the image MemberID = 4 records has been deleted successfully.

stored procedure In SQL Server

Conclusion

In this article, we saw how to create stored procedures in a SQL Server database for inserting, updating, and deleting records.

12 Comments

Join the conversation! Your thoughts help the community grow.

Gyan Kumar5y ago
Really nice explanation thanks!!!
Gyan Kumar5y ago
Good Explanation, thanks!!
-------6y ago
Great article sir but which situation create store procedure and which situation not create store procedure?
Ashfaque Baig7y ago
very nice....explained in very detail
Harsh Srivastava7y ago
What an amazing tutorial Ty!
Subin Thomas7y ago
Nice article thank you sir.
Assefa Aschalew8y ago
How to create c++ programming
Sreerag Sp8y ago
Thank you very much sir...
Rajesh Kumar8y ago
Nice article sir
- Manoj Kalla8y ago
  Thank You Rajesh Kumar Sir
Sumit Deshmukh9y ago
Really helpful..Thanks for Sharing Sir
- Manoj Kalla9y ago
  Thank you....sumit sir ji