Easy guidance for creating stored procedure

-


Here we learn how to create Stored Procedure.

Step 01 : Login SQL the SQL Server with server name and password 

Step 02 : Switch your database. 

 Once you login SQL Server, on the left hand side you could see Object Explorer , through this you can switch your database under the DATABASE  field. If you on click Database field it will list all the databases which are under your server.
Choose your database which you have decided to write SQL Stored procedure. Stored procedure section comes inside the Your_Database_Name -> Programmability -> Stored Procedure
When you on click the Stored Procedure Section , you can see Stored Procedure Option. Select it.


This will create empty stored procedure. It will looks like below code.

-- ================================================
-- 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:  
-- Create date: 
-- Description: 
-- =============================================
CREATE PROCEDURE  
 -- Add the parameters for the stored procedure here
 <@Param1, sysname, @p1>  = , 
 <@Param2, sysname, @p2>  = 
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

CREATE PROCEDURE command is used to create procedure , followed by a Stored procedure parameters and its name. The BEGIN and END are is used to define the query for the operation. Between this BEGIN and END  you have to write a SELECT, UPDATE , INSERT  or DELETE queries.

How to write comments in SQL SERVER

We can comment in SQL SERVER by
  1. -- (two hyphens) for single line of comment.
  2. Start with /* and end with */ for multiple line comments. 

Naming convention for Stored Procedure

Naming conventions are just to identify objects. By adding below prefixes in the name, we can clearly identify that this object is a stored procedure.
  1. sp
  2. stp
  3. stp_
  4. udstp
  5. udstp_
SQL command for creating a table into the SQL SERVER

USE [myweddingcar]  
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

Above code myweddingcar is my database name and tblMembers is my table name.

01 ) SELECT Stored Procedure

Click on your database and expand "Programmability" item and right click on "Stored Procedure" . In the query area between BEGIN and END, type your SELECT statement records from the table. 

Select * from tblMembers  

Press on F5 key or click on Execute button which is in task bar , you can save the store procedure. ANd after it you can see a message , " Command(s) completed successfully." 



Now you can go to Programmability ->Stored Procedures and right click on it and refresh. You could see the new stored procedure under the stored procedure option.

Execute Stored Procedure in SQL Server

Method 01 : Right click on new stored procedure which was previously created by you.

Here Stored Procedure               : To create new stored procedure
         Modify                               : To modify created stored procedure
         Execute Stored Procedure : To execute Stored Procedure.

Method 02:  
Switch to the Query window or CTRL + N to open new window and type the following command
  • Syntax = EXEC <Stored Procedure Name >
  • Example = EXEC stpGetAllDrivers

Output will be:

Parameters of Stored Procedure 

Parameters of stored procedures are used to pass input values and return output values.There are two types of parameters.
  1. Input parameters : Pass value to a Strode Procedure.
  2. Output parameters : Return values from a Stored Procedure

Create a SELECT query Stored Procedure with parameters

As we created to retrieve all values from table name Drivers, we can write a query which will retrieve specific parameters for table name UserLogin
 
-- ================================================
-- 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:  
-- Create date: 
-- Description: 
-- =============================================
CREATE PROCEDURE stpGetAllUsersByType 
 -- Add the parameters for the stored procedure here
 @Type varchar(50)
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 UserLogin where userType like '%' + @Type + '%'
END
GO

To run this Stored Procedure

Method 01: EXEC stpGetAllUsersByType @type = 'user'


Method 02 : Execute through UI

Right click on your stored procedure and click on execute stored procedure option.

 It output will be as below

INSERT 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:  
-- Create date: 
-- Description: 
-- =============================================
CREATE PROCEDURE stpCreateNewUser
 -- Add the parameters for the stored procedure here
 @username varchar(50),
 @usertype varchar(50),
 @password varchar(50),
 @email varchar (50)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 Insert into UserLogin (username,password,email,userType) Values (@username,@usertype, @email,@password)
END
GO
 
Execute above Stored Procedure as we explained earlier.

Update Stored Procedure

 
USE [myweddingcar]
GO
/****** Object:  StoredProcedure [dbo].[stpUpdateUserLogin]    Script Date: 6/10/2019 10:59:21 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  
-- Create date: 
-- Description: 
-- =============================================
CREATE PROCEDURE stpUpdateUserLogin
 -- Add the parameters for the stored procedure here
 @username varchar(50),
 @type varchar (50),
 @password varchar(50),
 @email varchar(50),
 @id int
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 UPDATE UserLogin set username=@username,userType=@type,password=@password,email=@email where id=@id
END
 
Execute this UPDATE Stored Procedure as I mentioned above.

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:  
-- Create date: 
-- Description: 
-- =============================================
CREATE PROCEDURE stpDeleteUserLogin
 -- Add the parameters for the stored procedure here
 @id int
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
 Delete from UserLogin where id=@id
END
GO
 
Execute this Stored Procedure as I explained above.

Comments

Post a Comment

Popular posts from this blog

Easy understanding of MVC design architecture

-
Image
There are lots of principle and methodology in software engineering field. Through this blog I am going to describe MVC design pattern. Real life example : Let's consider you are going to buy a suit for your wedding. You supposed to go your favorite tailor's shop and choose your desired suit's design which is shown by helper boy. Helper boy handed over the chosen design to that tailor( Controller ). Tailor collect the suitable items such as cloths material, needle, thread, button ,zip ( Model collect the information from database ).Then tailor stich the suit and give it to helper boy( VIEW ). Helper boy pack that suit with covered bag and give it to you. The same scenario is happening in MVC design architecture. MVC is an architectural design pattern. It's separate application into three main different components such as Model, View, Controller.  These components are having specific functionalities.  Model Model is simple class. It's contain busin
Read more

A / B Testing

-
Image
Introduction to A/B testing A/B testing is the method of comparing two versions of  a software or app against each other to indicate which one performs better. Every company has different marketing goals. We can't always ask our customers what they think of our site or emails. For this situation A/B testing plays the major role to improve our content and build a strategy. A/B testing also known as split tests. It's allow to compare two version of something to learn which is more effective. I simply conclude that do you like version A or version B? How A/B testing works  In A/B testing we take a webpage or app screen and modify it to create a second version of the same page. For example if you want to change the image and it's caption font, you may create four pages  Arial with images.  Arial without images. Times new roman with images. Times new roman without images.  These changes can be as simple as a single button, or complete redesign of the pa
Read more

Firewall

-
Image
We can easily said that firewall is a program which is prevents unauthorized access to or from a private network. Firewalls are tools that can be used to enhance the security of computers connected to a network, such as LAN or the Internet. They are an integral part of a comprehensive security framework for your network. What firewall do? Defend resources Validate access Manage and control network traffic Record and report on events Act as an intermediary A Personal firewall is important when You surf the Internet at home using an 'always on' broadband connection You connect to the Internet via a public WiFi network in a park, cafe or airport You run a home network which needs to be kept isolated from the Internet You wish to be kept informed when any program on your computer attempts to connect to the Internet Most Personal Firewalls are highly configurable so you can easily create security policies to suit your individual needs.
Read more