Create a Stored Procedure in SQL Server

The stored 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 of connecting our application to a database. We are going to use a stored procedure in retrieving and inserting data into the database.

This post will focus on steps on creating an actual stored procedure and hopefully, you can get idea’s on how it is being done.

  • 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

        USE [basicfunction]

        GO

        CREATE PROCEDURE [dbo].[SelectFromTable]

        AS

        BEGIN
                SELECT * FROM Listing

        END

Requirements:

  • SQL SERVER
  • SQL Server Management Studio

Make sure you have SQL server installed in your machine. If not you can download SQL server from this link.

Note: I’m using SQL Server Management Studio 2014

Let’s start:

Open SQL Server Management Studio. Connect to your SQL SERVER. Click 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. If not, create one by right-clicking on the Databases folder under your SQL Server. 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 the Table folder. Then right-click on the Table folder. Select a table to create a 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 fields 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

The next step will be adding dummy data for your table. This will be used to test if our stored procedure will be executed successfully. You can also use SQL Insert statement to Insert data.

Insert Statement

INSERT INTO Table(Column1,Column2,Column3) VALUES (Value1,Value2,Value3)

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. In the Object Explorer navigate to your database and proceed to the Programmability folder. Right-click on the folder and select the stored procedure option.

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:

        USE [basicfunction]
        GO
        SET ANSI_NULLS ON
        GO
        SET QUOTED_IDENTIFIER ON
        GO
        CREATE PROCEDURE [dbo].[SelectFromTable]
        AS
        BEGIN
                SELECT * FROM Listing
        END
  • 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 the database folder and reopen the stored procedure folder. Expand and check the procedure if it already exists. Right-click on your procedure and select Execute Stored Procedure.

Stored Procedure in SQL
Execute Stored Procedure

A dialog box will prompt asking for parameters needed for this query. Which we don’t have in our case. Press “OK” to execute.

Stored Procedure in SQL
Dialog Box

The result is shown below.

We should expect a result with all the data of the table. If not all data is displayed, check your select statement. There must somethings missing with the query.

Stored Procedure in SQL
Output

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

To learn how to call or use this procedure using ASP.NET MVC Application. Check out this blog Using SQL Stored Procedure in ASP.NET MVC Application

Leave a Comment

%d bloggers like this: