CODEWITHSUNDEEP
sql-serverstored-proceduresdynamic-sqlinsert-into
Freddy
Freddy

Reputation: 91

SQL Server 2014 execute stored procedure multiple times and Insert into table including parameter

I have a stored procedure that gets two parameters, Date and EquipName and the result set is not stored in a table.

What I want to do is to run the stored procedure multiple times for different EquipName and for each result set, INSERT into only one table (MyTable).

The result of the stored procedure has 30 rows.

To do that I am using below code.

Thanks to this Link

DECLARE @EquipName INT

DECLARE curs CURSOR LOCAL FAST_FORWARD FOR
    SELECT EquipName 
    FROM EquipTable 
    WHERE ...

OPEN curs

FETCH NEXT FROM curs INTO @EquipName

WHILE @@FETCH_STATUS = 0 
BEGIN
    INSERT INTO MyTable
        EXEC sp_storeProc  '2018-01-01', @EquipName

    FETCH NEXT FROM curs INTO @EquipName
END

CLOSE curs
DEALLOCATE curs

My question is:

Let's say, the stored procedure result has 5 columns but MyTable has one extra column NameOfEquip, and for every result set from the stored procedure, insert the @EquipName into all 30 rows of that result set.

How can I do that? MyTable is already created with right schema.

Upvotes: 3

Views: 4330

Answers (3)

PSK
PSK

Reputation: 17943

You can also achieve this using OPENROWSET. You can find more details on this here

Following is the example for the same.

For example you have a SP like following.

CREATE PROCEDURE TEST
(
 @INPUT1 VARCHAR(10),
 @INPUT2 VARCHAR(20)
)
AS
BEGIN
SELECT @INPUT1 AS COL1, @INPUT2 AS COL2
END

If you directly execute the SP 

EXECUTE TEST 'A','B'

You will get output like following.

    COL1    COL2
   ------  ------
    A        B

To append a extra parameter to the result you can try like following.

DECLARE @TBL TABLE(COL1 VARCHAR(10), COL2 VARCHAR(10), COL3 DATETIME)

INSERT INTO @TBL(COL1,COL2,COL3)
SELECT A.*,GETDATE() 
from openrowset('SQLNCLI','Server=(local);Trusted_Connection=yes;',
   'EXECUTE TEST ''A'',''B''') AS A

 SELECT * FROM @TBL

Output :

COL1    COL2    COL3
A        B      2018-02-21 09:09:03.257

Upvotes: 0

Sandhya
Sandhya

Reputation: 674

You can insert the results of the stored proc into a temp table, and then read from the temp table with the extra column and insert into 'MyTable'. Something like this:

DECLARE @TempTable TABLE (ColA INT, ColB INT)

INSERT INTO @TempTable
EXEC sp_storeProc  '2018-01-01', @EquipName

INSERT INTO MyTable
SELECT *, @EquipName FROM @TempTable

Upvotes: 0

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

After Each insert into the table, try updating the NameOfEquip for all values which are NULL. Maybe something Like this

WHILE @@FETCH_STATUS = 0 
BEGIN
    INSERT INTO MyTable
        EXEC sp_storeProc  '2018-01-01', @EquipName

UPDATE MyTable SET NameOfEquip = ISNULL(@EquipName,'') WHERE NameOfEquip IS NULL


    FETCH NEXT FROM curs INTO @EquipName
END

Upvotes: 1

Related Questions