Executing stored procedure for each line in table

Question

Good day,

I'm figuring out a legacy procedure and ran into something, which should be easy but does not compute with me, at least not today.

The logic I'm after is I have values in table1, which I wish to feed to stored procedure proc1 and save the results to Table2.

Currently this is called by different users manually filling in the strings which populate the variables. working syntax is for instance:

exec [dbo].[Proc1] @var1=N'10010015',@var2='2008-03-31 00:00:00', @var3=50000

I tried something like

declare var1 varchar, var2 varchar, var3 int
insert into table2 (result1, result2, result3) 
Select * from 
[dbo].[Proc1] (
  select @var1 = var1_tab1, 
         @var2 = var2_tab1 
         from table1
)

With regular function I'd just feed it the variables in correct order, but the procedure requires the @var1 formats. And I am not allowed to alter the function.

Previous try just gives me incorrect syntax. Any ideas how to approach this sort of issue would be appreciated. .

**Edit: **

Yeah, it's a duplicate. Or at least I used the other to create solution.

Way too much writing. Too long did not read. The following below worked.

Things that I did not understand, but now make sense:

-You can execute procedures without explicitly setting the variables as long as input is same order as the procedure definition.

-You can just insert the results to table because return (from this specific procedure) is select.

DECLARE @var1 int, 
@var2 int,
@var3 float,
@var4 datetime,

DECLARE cur CURSOR FOR SELECT var1, var2, var3, var4 FROM table1
OPEN cur

FETCH NEXT FROM cur INTO @var1, @var2, @var3, @var4

WHILE @@FETCH_STATUS = 0 BEGIN
    insert into table2   EXEC [procedure] @var1, @var2, @var3, 0, '', NULL, @var4
    FETCH NEXT FROM cur INTO @var1, @var2, @var3, @var4
END

CLOSE cur    
DEALLOCATE cur

Possible duplicate of [Loop through all the rows of a temp table and call a stored procedure for each row](https://stackoverflow.com/questions/22618968/loop-through-all-the-rows-of-a-temp-table-and-call-a-stored-procedure-for-each-r) — SMor, Feb 22 '18 at 12:22
Search before posting - always. [Passing columns](https://stackoverflow.com/questions/22618968/loop-through-all-the-rows-of-a-temp-table-and-call-a-stored-procedure-for-each-r/22619085) of a table as parameters to a stored procedure is a common question, as is [inserting](https://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table) the resultset. — SMor, Feb 22 '18 at 12:26
I believe what you are looking for is a function used with cross apply. You cannot call an SP in a select statement. Try writing your SP as a function. — Cetin Basoz, Feb 22 '18 at 12:50
What does this SP actually do? Interating through each row in a table is going to be slow. Dependingon what it's doing, it may well be easier to use something else, like a in-line table value function. Without knowing what `[proc1]` does though, I can't offer much more. — Thom A, Feb 22 '18 at 14:03

hkravitz · Answer 1 · 2018-02-22T12:50:07.320

It could be done using a dynamic SQL without a loop.

    DECLARE @T1 TABLE (Col1 NVARCHAR(10), Col2 NVARCHAR(10) , Col3 INT)
    /*Sample Data*/
    INSERT INTO @T1 (Col1 ,Col2, Col3 )
    SELECT '100100' , CONVERT(NVARCHAR(10),CAST(GETDATE() AS DATE),120), 5000
    UNION ALL
    SELECT '100200' , CONVERT(NVARCHAR(10),CAST(GETDATE()-1 AS DATE),120), 6000
    UNION ALL 
    SELECT '103200' , CONVERT(NVARCHAR(10),CAST(GETDATE()-2 AS DATE),120), 7000
    UNION ALL 
    SELECT '103400' , CONVERT(NVARCHAR(10),CAST(GETDATE()-3 AS DATE),120), 9000

    DECLARE @Sql NVARCHAR(MAX)=''
    SELECT @Sql+= 'EXEC YourSP @Var1 ='''+T.Col1+''' , @Var2 = '''+T.Col2+''' , @var3 = '+CONVERT(NVARCHAR(30),T.Col3)+';'+CHAR(10)     
    FROM @T1 T
    PRINT @sql
    EXEC sp_executesql @Sql

score 0 · Answer 2 · answered Feb 22 '18 at 12:46

Sample tables with data and Stored Procedure

IF OBJECT_ID('dbo.table1') IS NOT NULL
DROP TABLE table1

CREATE TABLE table1 (var1 VARCHAR(20),var2 VARCHAR(20),var3 INT)

INSERT INTO Table1 
SELECT '10010015','2008-03-31 00:00:00','50000' UNION ALL
SELECT '10010025','2008-03-21 00:00:00','40000' UNION ALL
SELECT '10010035','2008-03-29 00:00:00','50000' UNION ALL
SELECT '10010045','2008-03-30 00:00:00','60000' UNION ALL
SELECT '10010055','2008-03-28 00:00:00','70000'

IF OBJECT_ID('dbo.table2') IS NOT NULL
DROP TABLE table2
CREATE TABLE table2 (result1 VARCHAR(10),result2 VARCHAR(10),result3 INT )


IF OBJECT_ID('dbo.Proc1') IS NOT NULL
DROP PROCEDURE Proc1
GO
CREATE PROCEDURE Proc1
(
 @var1 VARCHAR(10)
,@var2 VARCHAR(20)
,@var3 INT
)
AS
BEGIN

INSERT INTO table2 (result1, result2, result3) 
SELECT @var1, @var2 ,@var3

END
GO

Using Dynamic Sql and While loop we will feed the parameters to sp one by in sequence saved the executed result in table2

DECLARE   @var1 varchar(10)
         ,@var2 varchar(20)
         ,@var3 INT 
         ,@SQL NVARCHAR(MAX)
         ,@MInID INT
         ,@MaxID INT

;WIth Cte
AS
(
SELECT ROW_NUMBER()OVER(ORDER BY var1) AS Id,* FROM table1
)
SELECT @MInID = MIN(ID ),@MaxID = MAX(ID ) FROM cte

WHILE (@MInID<=@MaxID)
BEGIN
        ;WIth Cte
        AS
        (
        SELECT ROW_NUMBER()OVER(ORDER BY var1) AS Id,* FROM table1
        )
        SELECT @var1=var1,@var2=var2,@var3=var3 
        FROM cte WHERE ID=@MInID

        SET @SQL='
        EXEC [dbo].[Proc1] @var1='''+CAST(@var1 AS VARCHAR(10))+''',@var2='''+CAST(@var2 AS VARCHAR(20))+''', @var3='+CAST(@var3 AS VARCHAR(10))
        PRINT @SQL
        EXEC(@SQL)
        SET @MInID=@MInID+1
END

Go

SELECT * FROM table2

Result

result1         result2          result3
------------------------------------------
10010015    2008-03-31 00:00:00  50000
10010025    2008-03-21 00:00:00  40000
10010035    2008-03-29 00:00:00  50000
10010045    2008-03-30 00:00:00  60000
10010055    2008-03-28 00:00:00  70000

score 0 · Answer 3 · edited Feb 22 '18 at 18:24

With such a sample data & procedure:

-- CREATE FAKE PROCEDURE AS AN EXAMPLE:
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyProcedure]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MyProcedure]
GO
CREATE  PROCEDURE [dbo].[MyProcedure] @A1 varchar(1),@A2 varchar(1),@id INT
AS BEGIN
    --switch chars, and multiplay intiger:
    SELECT @A2,@A1,@id*10
END
GO

--CREATE TABLES:
DECLARE @table1 TABLE (var1 VARCHAR(1), var2 VARCHAR(1), var3 INT)
DECLARE @table2 TABLE (res1 VARCHAR(1), res2 VARCHAR(1), res3 INT)
-- START DATA:
INSERT INTO @table1 VALUES ('a','b',1),('m','n',2),('x','y',3)
SELECT * from @table1

output @table1:

var1 var2 var3
---- ---- ----
a    b    1
m    n    2
x    y    3

The solution can be achieved by using a CURSOR:

DECLARE @var1 varchar(20)
DECLARE @var2 varchar(20)
DECLARE @var3 int

DECLARE @Cursor as CURSOR;
SET @Cursor = CURSOR FOR
    SELECT var1, var2, var3 
    FROM @table1

OPEN @Cursor;
FETCH NEXT FROM @Cursor INTO @var1, @var2, @var3;

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @table2 (res1, res2, res3)
        EXEC MyProcedure @var1, @var2, @var3

    FETCH NEXT FROM @Cursor INTO @var1, @var2, @var3;
END

CLOSE @Cursor;
DEALLOCATE @Cursor;


select * from @table2

Output: @table2*

res1 res2 res3
---- ---- ----
b    a    10
n    m    20
y    x    30

NOTE: Both two parts above should be run together as a single query

After all: Drop fake procedure:

DROP PROCEDURE [dbo].[MyProcedure]

Executing stored procedure for each line in table

3 Answers3