How to Create and Call a Stored Procedure in SQL?
Last Updated :
25 Oct, 2021
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.
Share your thoughts in the comments
Please Login to comment...