How to Create and Call a Stored Procedure in SQL?

With this article, we will learn how to Create and Call a Stored Procedure in SQL. For this article, we are going to use MSSQL as our database server.

What is a Stored Procedure?

A stored procedure is a pre-written SQL query that can be called multiple times and will run as the same. Like we can create a Stored procedure for Insert, select, update in SQL database. We can also pass parameters to the Stored procedures. So, we will create a database first:

Step 1: Creating Database

Query:

CREATE DATABASE GFG

Step 2: Using Database

Query:

USE GFG

Step 3: Create a table

Query:

CREATE TABLE gfgTutorial(
id integer,
Name varchar(20)
)

Step 4: Describe the table

Query:

sp_help 'dbo.gfgTutorial'

Output:

Created Table schema

Step 5: Insert some data into the table

Query:

INSERT INTO [dbo].[gfgTutorial]
           ([id]
           ,[Name])
     VALUES
           (1, 'Devesh')
GO

INSERT INTO [dbo].[gfgTutorial]
           ([id]
           ,[Name])
     VALUES
           (2, 'Geeks')
GO

INSERT INTO [dbo].[gfgTutorial]
           ([id]
           ,[Name])
     VALUES
           (3, 'For')
GO

INSERT INTO [dbo].[gfgTutorial]
           ([id]
           ,[Name])
     VALUES
           (4, 'Geeks')
GO

INSERT INTO [dbo].[gfgTutorial]E
           ([id]
           ,[Name])
     VALUES
           (5, 'GFG')
GO

Step 6: Create a Stored procedure for Select all the rows from a table

Query:

CREATE PROCEDURE select_all_data
AS
SELECT * FROM gfgTutorial
GO;

Output:

Successfully created the stored procedure

Execute Stored procedure select_all_data 

Query:

EXEC select_all_data

Output:

Executing stored procedure to select all data

Now we have seen how to create a basic stored procedure now let’s see how to create the parameterized stored procedure

Step 1: Create a parameterized stored procedure to insert data in the table

Query:

CREATE PROCEDURE insertData
@Name varchar(30), @id varchar(30)
AS
INSERT INTO gfgTutorial VALUES(@id, @Name)
GO

Step 2: Execute stored procedure

Query:

EXEC insertData @Name = 'Inserted Name', @id = 6

Data insertion successful

Check the data is inserted or not.

Data is inserted by the stored procedure.