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:
Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course by GeeksforGeeks.
Step 1: Creating Database
CREATE DATABASE GFG
Step 2: Using Database
Step 3: Create a table
CREATE TABLE gfgTutorial( id integer, Name varchar(20) )
Step 4: Describe the table
Step 5: Insert some data into the table
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
CREATE PROCEDURE select_all_data AS SELECT * FROM gfgTutorial GO;
Execute Stored procedure 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
CREATE PROCEDURE insertData @Name varchar(30), @id varchar(30) AS INSERT INTO gfgTutorial VALUES(@id, @Name) GO
Step 2: Execute stored procedure
EXEC insertData @Name = 'Inserted Name', @id = 6
Check the data is inserted or not.