Insert from stored procedure

Question

Hi

Can anyone help how to insert values to a table from proc including the input parameter passed to the procedure. I mean the @val and output of proc to a table

declare @val int = 1
insert into tab exec getBusinessLineHistory1 @val

Answer 1

You can find those informations here:

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver16#d-create-a-procedure-with-input-parameters

CREATE PROCEDURE HumanResources.uspGetEmployees  
    @LastName NVARCHAR(50),  
    @FirstName NVARCHAR(50)  
AS  
  
    SET NOCOUNT ON;  
    SELECT FirstName, LastName, JobTitle, Department  
    FROM HumanResources.vEmployeeDepartment  
    WHERE FirstName = @FirstName AND LastName = @LastName;  
GO  

In this example just change the SELECT statement with your INSERT statement.

Answer 2

Hi @SVA

You can do this with:

Declaring a Transact-SQL Variable

DECLARE @val INT;
SET @val = 1;

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/variables-transact-sql?view=sql-server-ver16#declaring-a-transact-sql-variable

Example:

DECLARE @val INT;  
SET @val = 1;  
  
SELECT BusinessEntityID, NationalIDNumber, JobTitle  
FROM HumanResources.Employee  
WHERE BusinessEntityID = @val;  

-------------------------------

If this is helpful please accept answer.

Answer 3

If you want the parameter values to be inserted as part of INSERT-EXEC, you will need to change the procedure to return the parameters as part of the result set. That is the only option.

Answer 4

Hi @SVA
You can create a temp table with the same structure as the procedure return，and then insert the procedure result into the temptable

-- prepare a local temp table to capture  
-- the results set displayed by the stored proc  
   
if object_id('tempdb..#soh') is not null  
    drop table #soh  
   
create table #soh(  
 SalesOrderID int identity(1,1) not null  
,OrderDate datetime not null  
,CustomerID int not null  
,SalesPersonID int null  
,TotalDue money not null  
)  
   
   
-- manipulate IDENTITY_INSERT property for  
-- #soh table while inserting rows  
-- from the stored procedure into the table  
set identity_insert #soh on  
insert into #soh  
  (  
    SalesOrderID  
   ,OrderDate  
   ,CustomerID  
   ,SalesPersonID  
   ,TotalDue  
  )  
exec uspMySecondStoredProcedure  
set identity_insert #soh off  
   
-- display values deposited into #soh  
select * from #soh  

Reference thread：https://www.mssqltips.com/sqlservertip/6141/save-sql-server-stored-procedure-results-to-table/#:~:text=The%20general%20solution%20for%20persisting%20a%20results%20set,tables%2C%20local%20temp%20tables%20and%20global%20temp%20tables.

Best Regards，
Isabella

Answer 5

Hi @SVA Here are two possible approaches：

1. Alter the SP and add the @val onto the final select clause. Like this: CREATE PROC test_proc @val int AS BEGIN SELECT @val ,COL1,COL2 FROM Table1 END INSERT INTO Target_Table EXEC test_proc @val = 1

2)Put the INSERT INTO TABLE_NAME VALUES(...) inside the SP, then execute the stored procedure directly. Like this：

CREATE PROC getBusinessLineHistory2
@val int 
AS
BEGIN
 WITH CTE AS
 (
  --Put the original SP code here
 )
 INSERT INTO Table_Name
 SELECT @val,Other_Columns
 FROM CTE
END

EXEC getBusinessLineHistory2 @val=1

Note: Both of the above methods are based on the premise that you have permission to create/alter stored procedures.

Best regards, LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.