How To Create A Stored Procedure In SQL Server Management Studio

In this article you will learn about how to create store procedure in sql server’s SQL SERVER MANAGEMENT STUDIO and very informative information and interview questions and answers about store procedure.

This article covers answers to the following questions,

• What is Store Procedure?
• Why do we use SET NOCOUNT ON in stored procedure?
• How many types of Store procedure are there?
• How to write comment in SQL SERVER?
• What is naming convention for store procedure?
• Step by Step for How to create SELECT QUERY base store procedure which return all records?
• How to execute store procedure in SQL SERVER?
• What is Parameter?
• Step by Step for How to create PARAMETER base SELECT QUERY store procedure which return records as per parameter passed.?
• Step by Step for How to create a INSERT QUERY base store procedure?
• Step by Step for How to create a UPDATE QUERY base store procedure?
• Step by Step for How to create a DELETE QUERY base store procedure?

What is Store Procedure?

A store procedure is a collection and set of sql statements and sql command logic which is compiled and stored in the database. A store procedure is one kind of database object which is available under programmability section.

We can reused one Store procedure where-ever we require it in programming because in store procedure we are writing processing, and insert and update, and delete.

Please refer to the following link to know more,

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql

Why do we use SET NOCOUNT ON in stored procedure?

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

NOCOUNT means do not count that is ON. 

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

How many types of Store procedure?

As database web application developers we mostly use two types of store procedures:

1. User defined store procedure.
2. System store procedure.

User defined store procedure

In programming mostly we are using this type of store procedure. We are creating this store procedure for receiving return value in tabular or scalar result. User defined store procedure can take input parameters and return output parameters. User defined store procedure is mixture of DDL (Data Definition Language) and DML (Data Manipulation Language ) commands.

For detail about DDL and DML please refer following link:

https://stackoverflow.com/questions/2578194/what-is-ddl-and-dml

User defined store is further classified into two types,

• T-SQL Store procedure
  As the name suggests, T-SQL (Transact SQL) which receives and returns parameter. Processed insert, update and delete query with parameter or without parameter. Returning the rows of output as per query result.
  
  
• CLR Store procedure
  CLR (Common Language Runtime) base store procedure which directly related to .NET framework. CLR stored procedure can be written in C#, VB.NET or in any other language which is supported by the .NET Framework. Because of this is written under CLR language thats why its MANAGED CODE. Its got full support and power of .NET framework.
  

System store procedure

Those store procedure used and run for managing administrative activities of SQL SERVER called System Store Procedure. 

Please refer following link to know more about Types of Store procedures,

https://technet.microsoft.com/en-us/library/ms187644(v=sql.105).aspx

STEP BY STEP SECTION

In this section we will do practical of following things,

• Step by Step for How to create SELECT QUERY base store procedure which return all records?
• Step by Step for How to create PARAMETER base SELECT QUERY store procedure which return records as per parameter passed.?
• Step by Step for How to create a INSERT QUERY base store procedure?
• Step by Step for How to create a UPDATE QUERY base store procedure?
• Step by Step for How to create a DELETE QUERY base store procedure?

Login in SQL SERVER with your Server Name, Login and Password.

Switch to your database, My Database(DB) name is MBKTest.

Empty template structure of store procedure will be created automatically by following the instructions given in image,

Empty template structure of store procedure

-- ================================================
-- 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 comment in SQL SERVER?

You can comment in sql server in the following ways,

1. -- (two hyphens / dash) for single line commenting.
2. start with /* ……. end with  */    for multi line commenting.

What is naming convention for store procedure?

We must follow same kind of Naming convention in practice or as per company coding policies.

For user defined store procedure naming convention my suggestions are as follows,

1. stp
2. stp_
3. udstp
4. udstp_

Naming convention is just to identify the object.

Please refer fto the ollowing link for more knowledge comment,

https://technet.microsoft.com/en-us/library/ms188621(v=sql.105).aspx

My Table Name is “tblMembers” and following is table structure of tblMembers,

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

Step by Step for How to create SELECT QUERY base store procedure which return all records?

A very simple kind of store procedure which returns all records of table and joining tables.

Click on your Database and expand “Programmability” tab and right click on “Store Procedures” or press CTRL + N to get new query window with current connection.

Store procedure CODE for return all records of table.

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;

    -- Insert statements for procedure here
    Select * from tblMembers
END
GO

After writing above code press F5 or click on EXECUTE button see image for more detail.

As you can see in image if your store procedure is saved successfully in Messages window you will get message “Command(s) completed successfully.”

Now again click on Programmability -->Store Procedure and right click on store procedure and select REFRESH.

You can see in image store procedure called stpGetAllMembers is created.

How to execute store procedure in SQL SERVER?

Or simply type following,

To run store procedure in SQL Server management studio switch to Query window or CTRL +N to open an new query window and type the following command. 

• Syntax - EXEC <store procedure name>
• Example - EXEC stpGetAllMembers

For more detail you can visit the following link,

https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/execute-a-stored-procedure

Now we run our store procedure called stpGetAllMembers

In sql server management studio switch to query window or press CTRL + N

OUTPUT

What is Parameter?

Exchanging data between store procedure.

Basically there are two parameters,

1. Input Parameter - Here we pass the value to store procedure.
2. Output Parameter - Here we receive the value from store procedure.

Please visit the following link for more detail,

https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/parameters

Step by Step for How to create PARAMETER base SELECT QUERY store procedure which return records as per parameter passed.?

In the previous step by step you learned how to create a simple store procedure which returns all records and learned how to execute on SQL Server Management Studio.

In current section you will learn how to create a store procedure with parameter and execute the same with parameter.

Here Parameter means we want or check desired records is available in table or not by sending parameter value inside store procedure.

Click on your Database and expand “Programmability” tab and right click on “Store Procedures” or press CTRL + N to get new query window with current connection and type following codes.

Store procedure CODE for return specific records which satisfy the condition of parameters in table.

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

Now we run our store procedure called stpGetMembersByCityName

Refresh the Database or Programmability option, you can easily see your stpGetMembersByCityName

Store procedure under Store procedure option.

You can run above store procedure with coding / manually or UI.

By Coding / Manually

EXEC GetMemberByCityName   @CityName = 'mal'

By UI means store procedure will execute your procedure with required parameter.

Folllowing is written by sql server management studio in behind to run and execute the store procedure.

USE [MBKTest]
GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[GetMemberByCityName]
        @CityName = N'mal'

SELECT  'Return Value' = @return_value

GO

OUTPUT

Step by Step for How to create a INSERT QUERY base store procedure?

In this step by step you will learn how to insert an new record with the help of store procedure. We will going to write INSERT query inside store procedure.

In the following code you can see there is no mention of MEMBER ID column parameter because that field / column is set as identity column and auto increment.

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 Store Procedure inside Object Explorer of your Database and select REFRESH

Pass the value of parameter in Execute dialog box. Please refer to the following screen shot,

Following code is automatically written by SQL SERVER MANAGEMENT STUDIO,

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 query window you can check by query for above record to see if  Mahesh Chand sir is created or not.

By using Manual you can run and insert store procedure by this way,

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

OUTPUT

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

Step by Step for How to create a UPDATE QUERY base store procedure?

In this step by step you will learn how to update an existing record with the help of store procedure by passing ID of record. We are going to write UPDATE query inside store procedure.

In the following code you can see the code mentions MEMBER ID column parameter

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 Store Procedure inside Object Explorer of your Database and select REFRESH

RUN UPDATE STORE PROCEDURE BY UI

Now again right click on store procedure and select Execute Store procedure…

Following code is automatically written by SQL SERVER MANAGEMENT STUDIO,

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

RUN UPDATE STORE PROCEDURE BY MANUALLY (CODING)

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

You can check previous image and see there are other details on Member ID = 20,17, Now it's updated.

Step by Step for How to create a DELETE QUERY base store procedure?

In this step by step you will learn to delete a record through store procedure. So far now you have seen how to  Insert and update the record via store procedure.

Delete Store Procedure Code

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

Right click on Store Procedure inside Object Explorer of your Database and select REFRESH

RUN STORE PROCEDURE BY UI

Now again right click on store procedure and select Execute Store procedure…

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

RUN DELETE STORE PROCEDURE BY MANUALLY (CODING)

EXEC stpDeleteMemberByMemberID  2

OUTPUT

You can see in image MemberID = 4 record has deleted successfully.