How to Insert into Table from Stored Procedure with Parameters

by

When you have SQL Code you want to use repeatedly, the stored procedure will help. It is a set of codes specifically written to perform a particular task. We can write and execute a query with a single line of code.

If the user wants to insert rows in a table, they don’t need to write full syntax to insert. We can create a stored procedure and execute it in a single line.

This article will discuss the SQL Server stored procedure to insert into Table.

Insert Data into the Table using the Stored Procedure

As a first and foremost step, you must create a table to proceed.

Here, I have created a table called Product Details with the column ProductId, product name, and order date.

CREATE TABLE ProductDetails (
   ProductID INT IDENTITY(1,1) PRIMARY KEY,
   ProductName NVARCHAR(50),
   OrderDate DATE
);

Now, we will add values to the table using the stored procedure.

CREATE PROCEDURE Details
 @ProductName NVARCHAR(50),
   @OrderDate DATE
   AS
BEGIN
    INSERT INTO ProductDetails (ProductName,OrderDate)
    VALUES (@ProductName, @OrderDate);
END;


EXEC Details
    @ProductName = 'laptop', 
    @OrderDate = '2020-06-01';
     

	select * from ProductDetails
Insert into table using parameters

Insert into Temporary Table using Stored Procedure

Now, we will see how to insert into a temporary table using the stored procedure. Look at the below to do so.

First, we will create a temporary table and insert values into it. Here, I have the columns Order Id and Order Name.

USE tempdb
GO
DROP TABLE IF EXISTS dbo.#tempTable
CREATE TABLE dbo.#tempTable(
[Order ID] int,
[Order Name] nchar(20)
)

CREATE PROCEDURE InsertTem @ID int, @Name nchar(20)
AS
BEGIN
	INSERT INTO dbo.#tempTable([Order ID], [Order Name])
	VALUES(@ID, @Name)
END

Once the temporary table is created and values are added, execute the syntax below.

EXEC [tempdb].[dbo].[InsertTem] 101, 'Fabric'
SELECT * FROM [tempdb].[dbo].[#tempTable]
Insert into table using stored procedure parameter

Insert into Table returns Multiple Result

Let’s see how to insert into a table using a stored procedure that returns multiple results.

Below is the syntax for the result set.

Execute procedure name
With RESULT SETS {undefined|none|resultset definition}

undefined – If we run the procedure by default, we will get the result as undefined because we will get output based on the selection.

none – If the result we set empty, it will return none.

Now, I will create the procedure called pemp for the table EmpDetails.

Create Procedure Pemp
As
Begin
select EmployeeId, EmpName, Role from EmpDetails
Order By EmployeeId
End
Insert into stored procedure with result sets

If you are using result sets none, it will throw an error. This is because in the EmpDetails table, we have more column values than we have selected. See the image below for understanding.

Insert into Stored Procedure with result none

Now, we will try changing the column name using the query below.

Exec Pemp
Go
Exec Pemp
With Result sets 
(
(ID int,
Name varchar (30),
Roles char (1))
)
Insert into stored procedure with result set

In the output, you can see the column name has been changed for the same column.

Here, you can alter the column name by taking another example. Here I have chosen only EmpName and Role. Below is the code and output.

Alter Procedure Pemp
As
Begin
select EmployeeId, EmpName, Role from EmpDetails
Order By EmployeeId
End

Exec Pemp
Go
Exec Pemp
With Result sets 
(
(ID int,
Name varchar (30),
Roles char (1))
,
(Name2 varchar (30),
Roles2 char (1))
)
Insert into Stored procedure using parameter

This is how we can get Multiple results set by inserting them into a table using the stored procedure.

Benefits of using Stored Procedure

Below are the major benefits of using stored procedures.

  • Code Reusability – Stored procedures can be reused across multiple applications and scenarios.
  • Improvement in Performance – They improve performance by reducing network traffic and benefiting from cached execution plans.
  • Data Security – They help secure the data by controlling access and reducing the risk of SQL injection.

Conclusion

I trust this tutorial helped you learn to insert into a table using stored parameters. Getting the result set with altered values also touches upon this.

Read Also,