SQL Stored Procedure

stored-procedure-sql

Stored Procedure In SQL Server

In this article you will learn about how to create a stored procedure in SQL. This article covers answers to the following questions,
  1. What is a stored procedure in SQL?
  2. Why do we use SET NOCOUNT ON in a stored procedure?
  3. How many types of stored procedure are there?
  4. How to write comments in SQL Server?
  5. What are the naming conventions for stored procedures?
  6. How to create a stored procedure to select data from a database tabe using SELECT SQL query?
  7. How to execute stored procedures in SQL Server?
  8. What is are parameters in stored procedures?
  9. How to create parameters in a SELECT query stored procedure which return records as per parameter passed?
  10. How to create an INSERT query based stored procedure?
  11. How to create an UPDATE query based stored procedure?
  12. How to create a stored procedure to delete records using DELETE query?

What is a Stored Procedure?

A SQL stored procedure (SP) is an assortment SQL proclamations and sql order rationale, which is gathered and stored on the database. Stored procedues in SQL permits us to make SQL questions to be stored and executed on the worker. Stored procedures can likewise be reserved and reused. The primary reason for stored procedures to conceal direct SQL inquiries from the code and improve execution of database activities, for example, select, update, and delete data.

Why do we use SET NOCOUNT ON in a 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 to know what happened when SET NOCOUNT OFF.

Types of stored procedures

There are two types of stored procedures available in SQL Server:
  1. User defined stored procedures
  2. System stored procedures

User defined stored procedures

User defined stored procedures are created by database developers or database administrators. These SPs contains one more more SQL statements to select, update, or delete records from database tables. User defined stored procedure can take input parameters and return output parameters. User defined stored procedure is 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 returns 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 procedyres are created and executed by SQL Server for the server administrative activities. Developers usually don’t interfere with system SPs.

Login to SQL Server database

Let’s login to our SQL Server database, so we can achieve the folowing:
  • How to create a SELECT QUERY based stored procedure which return all records?
  • How to create a PARAMETER based SELECT QUERY stored procedure which return 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?
Login in SQL SERVER with your Server Name, Login and Password.
stored procedure In SQL Server
Switch to your database. My database name is MBKTest.
stored procedure In SQL Server
Empty stored procedure will be created using the following:
stored procedure In SQL Server
The unfilled layout made by SQL Server for a SP resembles the accompanying. The CREATE PROCEDURE SQL order is utilized to make a procedure, trailed by a SP name and its boundaries. The BEGIN and END territory is utilized to characterize the inquiry for the activity. This is the place where you will compose a select, update, embed, or delete inquiries.
  1. — ================================================
  2. — Template generated from Template Explorer using:
  3. — Create Procedure (New Menu).SQL
  4. — Use the Specify Values for Template Parameters 
  5. — command (Ctrl-Shift-M) to fill in the parameter 
  6. — values below.
  7. — This block of comments will not be included in
  8. — the definition of the procedure.
  9. — ================================================
  10. SET ANSI_NULLS ON
  11. GO
  12. SET QUOTED_IDENTIFIER ON
  13. GO
  14. — =============================================
  15. — Author:      <Author,,Name>
  16. — Create date: <Create Date,,>
  17. — Description: <Description,,>
  18. — =============================================
  19. CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
  20.     — Add the parameters for the stored procedure here
  21.     <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
  22.     <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
  23. AS
  24. BEGIN
  25.     — SET NOCOUNT ON added to prevent extra result sets from
  26.     — interfering with SELECT statements.
  27.     SET NOCOUNT ON;
  28.     — Insert statements for procedure here
  29.     SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
  30. END
  31. GO

How to write comments in SQL SERVER?

You can comment in sql server in the following ways,
  1. — (two hyphens / dash) for a single line of comment.
  2. 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.
  1. sp
  2. stp
  3. stp_
  4. udstp
  5. udstp_
Naming conventions are just to identify objects. By adding these prefixes in 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 column where the first column is an idenity column. Once the table is created, open table in your SSMS and add some data by manually entering data to the table.
  1. USE [MBKTest]
  2. GO
  3. /****** Object:  Table [dbo].[tblMembers]    Script Date: 18-Nov-17,Sat 6:47:55 PM ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. SET ANSI_PADDING ON
  9. GO
  10. CREATE TABLE [dbo].[tblMembers](
  11.     [MemberID] [int] IDENTITY(1,1) NOT NULL,
  12.     [MemberName] [varchar](50) NULL,
  13.     [MemberCity] [varchar](25) NULL,
  14.     [MemberPhone] [varchar](15) NULL
  15. )
  16. GO
  17. SET ANSI_PADDING OFF
  18. GO

How to create a SELECT stored procedure?

Click on your Database and expand “Programmability” item and right click on “Stored Procedures” or press CTRL + N to get 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.
  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5. — =============================================
  6. — Author:      Manoj Kalla
  7. — Create date: 18th Nov 2017
  8. — Description: Return all members
  9. — =============================================
  10. –Store procedure name is –> stpGetAllMembers
  11. CREATE PROCEDURE stpGetAllMembers
  12. AS
  13. BEGIN
  14.     — SET NOCOUNT ON added to prevent extra result sets from
  15.     — interfering with SELECT statements.
  16.     SET NOCOUNT ON;
  17.     — Select statements for procedure here
  18.     Select * from tblMembers
  19. END
  20. GO
Now, press F5 or click on Execute button to execute the SP.
stored procedure In SQL Server
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 below UI, right click on the SP name and select Execute Stored Procedure… to execute a SP. From here, you can also modify an exisitng SP.
stored procedure In SQL Server
Alternatively, you can also execute a SP from the Query window.
To run stored 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 <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 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. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5. — =============================================
  6. — Author:      Manoj Kalla
  7. — Create date: 20-Nov-2017
  8. — Description: Return specifc city records
  9. — =============================================
  10. CREATE PROCEDURE stpGetMembersByCityName
  11.     — Add the parameters for the stored procedure here
  12.     @CityName nvarchar(30)
  13. AS
  14. BEGIN
  15.     — SET NOCOUNT ON added to prevent extra result sets from
  16.     — interfering with SELECT statements.
  17.     SET NOCOUNT ON;
  18.     Select * From tblMembers
  19.     where MemberCity like ‘%’+@CityName+‘%’
  20. END
  21. GO
Execute it.
To run this SP, type the following command in 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:
  1. USE [MBKTest]
  2. GO
  3. DECLARE @return_value int
  4. EXEC    @return_value = [dbo].[GetMemberByCityName]
  5.         @CityName = N‘mal’
  6. SELECT  ‘Return Value’ = @return_value
  7. GO
OUTPUT
stored procedure In SQL Server

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:      Manoj Kalla
  7. — Create date: 20-Nov-2047
  8. — Description: To create a new member
  9. — =============================================
  10. CREATE PROCEDURE stpInsertMember
  11. @MemberName varchar(50),
  12. @MemberCity varchar(25),
  13. @MemberPhone varchar(15)
  14. AS
  15. BEGIN
  16.     — SET NOCOUNT ON added to prevent extra result sets from
  17.     — interfering with SELECT statements.
  18.     SET NOCOUNT ON;
  19.     Insert into tblMembers (MemberName,MemberCity,MemberPhone)
  20.            Values (@MemberName,@MemberCity, @MemberPhone)
  21. END
  22. GO
Right click on stored procedure in Object Explorer and select Refresh.
stored procedure In SQL Server
Pass the value of 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.
  1. USE [MBKTest]
  2. GO
  3. DECLARE @return_value int
  4. EXEC    @return_value = [dbo].[stpInsertMember]
  5.         @MemberName = N‘Mahesh Chand’,
  6.         @MemberCity = N‘NewYork’,
  7.         @MemberPhone = N‘9999945121’
  8. SELECT  ‘Return Value’ = @return_value
  9. 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 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 quert based stored procedure?

Let’s create a new SP that will update a table records 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.
  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5. — =============================================
  6. — Author:      Manoj Kalla
  7. — Create date: 20-Nov-2017
  8. — Description: Update a member detail by ID
  9. — =============================================
  10. CREATE PROCEDURE stpUpdateMemberByID
  11. @MemberID int,
  12. @MemberName varchar(50),
  13. @MemberCity varchar(25),
  14. @MemberPhone varchar(15)
  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.     UPDATE tblMembers
  21.     Set MemberName = @MemberName,
  22.         MemberCity = @MemberCity,
  23.         MemberPhone = @MemberPhone
  24.     Where MemberID = @MemberID
  25. END
  26. GO
Right click on stored procedure in the Object Explorer and select Refresh. You will see the SP is created.
Now, Right click on 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.
  1. USE [MBKTest]
  2. GO
  3. DECLARE @return_value int
  4. EXEC    @return_value = [dbo].[stpUpdateMemberByID]
  5.         @MemberID = 20,
  6.         @MemberName = N‘Nirupama Kalla’,
  7.         @MemberCity = N‘Mumbai’,
  8.         @MemberPhone = N‘904512541xxxx’
  9. SELECT  ‘Return Value’ = @return_value
  10. 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 a SP that will delete records. The new SP uses a DELETE command and delete all records that matches provided Member ID.
  1. SET ANSI_NULLS ON
  2. GO
  3. SET QUOTED_IDENTIFIER ON
  4. GO
  5. — =============================================
  6. — Author:      Manoj Kalla
  7. — Create date: 21-Nov-2017
  8. — Description: Delete a Member by Member ID
  9. — =============================================
  10. CREATE PROCEDURE stpDeleteMemberByMemberID
  11.     @MemberID int
  12. AS
  13. BEGIN
  14.     — SET NOCOUNT ON added to prevent extra result sets from
  15.     — interfering with SELECT statements.
  16.     SET NOCOUNT ON;
  17.     Delete from tblMembers
  18.     where MemberId = @MemberID
  19. END
  20. 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 @MemberID parameter value = 4.
stored procedure In SQL Server
RUN DELETE stored procedure BY MANUALLY (CODING)
EXEC stpDeleteMemberByMemberID  2
OUTPUT
You can see in image MemberID = 4 record has been deleted successfully.
stored procedure In SQL Server
In this article, we saw how to create stored procedures in a SQL Server database for inserting, updating, and deleting records.