Stored Procedure In SQL Server
- What is a stored procedure in SQL?
- Why do we use SET NOCOUNT ON in a stored procedure?
- How many types of stored procedure 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 tabe using SELECT SQL query?
- How to execute stored procedures in SQL Server?
- What is are parameters in stored procedures?
- How to create parameters in a SELECT query stored procedure which return records as per 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 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?
Types of stored procedures
- 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 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.
System stored procedures
Login to SQL Server database
- 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?



- — ================================================
- — 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?
- — (two hyphens / dash) for a single line of comment.
- start with /* ……. end with */ for multiline comments.
What is the naming convention for stored procedures?
- sp
- stp
- stp_
- udstp
- udstp_
Create a database 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?
- 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



Execute stored procedures in SQL Server

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

What are parameters in stored procedures?
- 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?
- 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

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

How to create a INSERT query based stored procedure?
- 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


- 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



How to create an UPDATE quert based stored procedure?
- 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

- 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

How to create a DELETE query based stored procedure?
- 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

