In this SQL Server tutorial, I will show you how to execute stored procedure for insert in SQL Server.
You will understand how to create a stored procedure for inserting data and execute it using the query and SQL Server Management Studio.
Execute Stored Procedure for Insert in SQL Server
Before executing the stored procedure for inserting data in SQL Server, first you need to create it.
First create a table Users that will contain the user information such as UserID, UserName, CreationDate, and IsActive. So, use the below query to create the Users table.
CREATE TABLE Users (
UserID INT PRIMARY KEY,
UserName NVARCHAR(255) NOT NULL,
CreationDate DATETIME NOT NULL,
IsActive BIT NOT NULL
);
GO
After creating the Users table, now create a stored procedure to insert the user data, for that use the below query.
CREATE PROCEDURE InsertUser
@UserID INT,
@UserName NVARCHAR(255),
@CreationDate DATETIME,
@IsActive BIT
AS
BEGIN
INSERT INTO Users (UserID, UserName, CreationDate, IsActive)
VALUES (@UserID, @UserName, @CreationDate, @IsActive)
END;
GO
When the above query is executed, it creates a new stored procedure named ‘InsertUser’ with four parameters @UserID of type INT, @UserName of type NVARCHAR(255), @CreationDate of type DATETIME and @IsActive of type BIT.
After that within BEGIN and END blocks, the INSERT INTO statement is specified to insert the new user data based on the provided values through the four parameters.
Execute the InsertUser procedure to insert new user details using the below query.
EXEC InsertUser
@UserID = 1,
@UserName = 'Rodney',
@CreationDate = '2023-11-01 10:00:00',
@IsActive = 1;
GO
The above query inserts the new user details when the InsertUser procedure is executed using the EXEC statement with the parameter value. In this case, the new user is Rodney.
Here, you need to understand how parameter value is provided to the InsertUser procedure. Each parameter with its value is separated by a comma.
After execution of the above procedure, you can view the Users table using the below query.
SELECT * FROM Users;
GO
As you can see from the above picture, you have successfully inserted the data into the Users table using the UserInsert stored procedure. This is how to execute the stored procedure for insert in SQL Server using the query.
Execute Stored Procedure for Insert in SQL Server Using SSMS
If you want to execute the stored procedure for inserting the data into a table using SQL Server Management Studio, you can do that, too.
If you have followed the above section for creating a stored procedure, follow the below step to execute the stored procedure.
Then, expand the Databases node from the Object Explorer panel. After that within Databases, the node expands the E_Commerce (in your case, it can be any database), then expands the Programmability subnode, and then expands the Stored Procedures subfolder as shown below.
- Databases>E_Commerce>Programmability>Stored Procedures
Then right-click on the dbo.InserUser stored procedure and select the Execute Stored Procedure option, as shown in the below picture.
When you select the Execute Stored Procedure option, then an Execute Procedure dialog appears where you need to provide the user value for the parameters, as shown in the below picture.
Look in the above output within the value column; four value is provided for each corresponding parameter. After entering the new user value, click on the OK button to execute the InsertUser stored procedure.
After executing the stored procedure, you see the output as shown below.
If your output looks like the one shown above, then you have successfully inserted the user data into the Users table using the InserUser stored procedure through SSMS.
To view the Users table, use the below query.
SELECT * FROM Users;
GO
As you can see, a new user named Paul is in the Users table. So this is how to execute the stored procedure for insertion in SQL Server using the SSMS.
Conclusion
In this SQL Server tutorial, you learned how to execute stored procedures for insert in SQL Server. Where you learned two ways to execute the stored procedure using query and SSMS.
You may also like:
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.
