In this article you will come to know about how to create store procedure in sql server’s SQL SERVER MANAGEMENT STUDIO and very informative information and interview questions answers about store procedure.
SQL: Structured Query Language
This article cover answers of following questions:
What is Store Procedure?
Why do we use SET NOCOUNT ON in stored procedure?
How many types of Store procedure?
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 collection and set of sql statement and sql commands logic which is compiled and stored in 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 required in programming because in store procedure we are writing processing, insert and update, delete etc.. . .
Please refer following link to know more:
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 webapplication developer we mostly used two types of store procedures:
1. User defined store procedure.
2. System store procedure.
1. 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 mixer of DDL (Data Definition Language) and DML (Data Manipulation Language ) commands.
For detail about DDL and DML please refer following link:
User defiend store further classified into two types:
A. T-SQL Store procedure: As name suggest T-SQL (Transact SQL) which receive and return parameter. Processed insert, update and delete query with parameter or without parameter. Returning the rows of output as per query result.
B. 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.
2. 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:
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.
 |
| SQL Server Training Institute Mumbai |
Switch to your database, My Database(DB) name is MBKTest.
 |
| SQL Server Training Institute Mumbai |
Empty template structure of store procedure will be create automatically by follow instruction given in image:
 |
| Sql Server and Asp.net training institute in mumbai |
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 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 suggestion are followings:
1. stp
2. stp_
3. udstp
4. udstp_
Naming convention is just to identify the object.
Please refer following link for more knowledge comment:
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?
Very simple kind of store procedure which return 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.
 |
| Sql Server and Asp.net training institute in mumbai |
As you can see in image if your store procedure 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.
 |
| Sql Server and Asp.net training institute in mumbai |
How to execute store procedure in SQL SERVER?
 |
| Sql Server and Asp.net training institute in mumbai |
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 following command.
Syntax: EXEC <store procedure name>
Example: EXEC stpGetAllMembers
For more detail you can visit following link:
Now we run our store procedure called stpGetAllMembers
In sql server management studio switch to query window or press CTRL + N
OUTPUT:
 |
| Sql Server and Asp.net training institute in mumbai |
What is Parameter?
Exchanging data between store procedure.
Basically there is two of 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 following link for more detail:
Step by Step for How to create PARAMETER base SELECT QUERY store procedure which return records as per parameter passed.?
In previous step by step you learn how to create simple store procedure which return 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 require parameter.
 |
| Sql Server and Asp.net training institute in mumbai |
Folllowing written by sql server management studio in behind to run 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 :
 |
| Sql Server and Asp.net training institute in mumbai |
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.
Following code you can see there not mention 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
 |
| Sql Server and Asp.net training institute in mumbai |
Pass the value of parameter in Execute dialog box. Please refer the following screen shot:
 |
| Sql Server and Asp.net training institute in mumbai |
Following code 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 of Mahesh Chand sir is created or not.
By Manually you can run insert store procedure by this way:
EXEC stpInsertMember @MemberName = 'Suhana & Ashish Kalla ', @MemberCity = 'Mumbai ', @MemberPhone = N'9022592774xxx'
 |
| Sql Server and Asp.net training institute in mumbai |
OUTPUT:
You can check “Suhana & Ashish Kalla” record added successfully.
 |
| Sql Server and Asp.net training institute in mumbai |
Step by Step for How to create a UPDATE QUERY base store procedure?
In this step by step you will learn how to update a existing record with the help of store procedure by passing ID of record. We will going to write UPDATE query inside store procedure.
Following code you can see the code there mention MEMBER ID column parameter because ID basis we will update the record.
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…
 |
| Sql Server and Asp.net training institute in mumbai |
Following code 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 check previous image there is other detail on Member ID = 20,17, Now its updated.
 |
| Sql Server and Asp.net training institute in mumbai |
Step by Step for How to create a DELETE QUERY base store procedure?
In this step by step you will learn delete a record through store procedure. Till now you had seen Insert, 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 image I had passed @MemberID parameter value = 4.
 |
| Sql Server and Asp.net training institute in mumbai |
RUN DELETE STORE PROCEDURE BY MANUALLY (CODING)
EXEC stpDeleteMemberByMemberID 2
OUTPUT:
You can see in image MemberID = 4 record deleted successfully.
 |
| Sql Server and Asp.net training institute in mumbai |
Happy Coding. . .
I provide personalized training through home tuition or online on Dot Net (.Net) , Asp.Net [WebForm / MVC], WCF, WPF, WebService, Windows Application, Console Application.
If you liked this post, I’d be very grateful if you’d help it spread by emailing it to a friend, or sharing it on Twitter or Facebook or Google+.
Leave a comment that starts a conversation.
Thank you!
No comments:
Post a Comment