How to create Stored Procedure in SQL Server

Store procedure is composed of Structured Query Language (SQL) which is found in a Relational Database Management System. Stored Procedure is used to store SQL queries that are mainly used to modify data from a Database. This is an alternative way in connecting our application to a database. In this tutorial Instead of embedding SQL Queries in our application we are going to use a stored procedure in processing data from and to our database.

To draw the picture in your mind in what we are about to achieve in this tutorial below is a sample code for Creating Store Procedure

  • basicfunction -> Database name
  • [dbo].[SelectFromTable] -> Name of store procedures
  • SELECT * FROM Listing -> SQL Query that select all data from table Listing

Create Procedure sample code

Requirements:

  • SQL SERVER
  • SQL Server Management Studio

Before we proceed with the steps below you need to install the requirements listed above.

Note: I’m using SQL Server Management Studio 2014

Let’s start by opening our SQL Server Management studio. Then connect to your SQL SERVER by clicking on the icon from the top menu of your SQL Object explorer.

Stored Procedure in SQL

Connect to server

Select Server name you want to connect from the pop dialog box shown below.

Stored Procedure in SQL

Select Server

I assume you have already created a database but If not, create one by right clicking on the Databases folder under your SQL Server. Then Click on New Database and proceed with naming your database.

Stored Procedure in SQL

Create new database

After that, create a table where we can point our stored procedure If you have an existing table proceed with the next step. If not expand your Database and navigate to Table folder, then right click on the folder and select table to create new Table for your Database.

See image below for your guide.

Stored Procedure in SQL

Create Table

In the next step proceed with the design for your table. Input necessary field or columns you will use for your table. If you have something in your mind you can design your own or proceed with the designed presented in the image below.

Stored Procedure in SQL

Table Design

Next step will be adding dummy data for your table. This will be use to test if our stored procedure will be executed successfully. You can also use SQL Query to insert data to your table using the format below:

Or you can proceed by right clicking on the table in the SQL Server Object Explorer and choosing Edit Top 200 Rows just like the image shown below.

Stored Procedure in SQL

Edit top 200 Rows

Now in middle pane of your SQL Server Management Studio input sample data for your table

Stored Procedure in SQL

Insert Data

Creating Store Procedure

To go with the main objective of this tutorial, let’s proceed in creating the actual Stored Procedure. From the Object Explorer of your SQL Server Management Studio Expand Databases folder again and navigate to your Database, then proceed to Programmability folder (two folder below your Table Folder). Right click on the folder and select Stored Procedure.

Stored Procedure in SQL

Create Stored Procedures

In the middle pane of your SQL Server Management Studio a new window will be open with the default value needed for the procedure to work. You can modify it and manually encode the Query you want to execute or just simply copy the code below.

Code:

 

  • basicfunction -> Database name
  • [dbo].[SelectFromTable] -> Name of store procedures
  • SELECT * FROM Listing -> SQL Query that select all data from table Listing

To execute command simply hit f5 or click on Execute button located on the top navigation bar.

Stored Procedure in SQL

Execute Procedure

To know if your Procedure is successfully created you must see the successful notification. See image below.

Stored Procedure in SQL

Success Notification

Testing your Stored Procedures

After successfully creating Stored Procedure you should now be able to see your procedure under the folder named Stored Procedures. If ever you are not able to see it. Refresh Database and Navigate back to the Stored Procedures folder and then Right Click on your procedure and select Execute Stored Procedure.

Stored Procedure in SQL

Execute Stored Procedure

A dialog box will pop-up containing previews of your procedure’s parameter, but in this case we don’t have any parameter included in our procedure so just simply hit on OK button to proceed.

Stored Procedure in SQL

Dialog Box

The result is shown below. You should see all data of your Table since we use Select * statement from our Stored Procedure query.

Stored Procedure in SQL

Output

Congratulation you have successfully created a SQL Stored Procedure. Thank you for reading hopes this help. Happy coding!!

To learn how to call this procedure from your ASP.NET MVC Application Proceed with this blog Using SQL Stored Procedure in ASP.NET MVC Application

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *