SQL Server : calling procedure iteratively

Question

I have a stored procedure which evaluates something. I would like to call this procedure many times with different arguments and insert everything into one table. Calling this procedure is what I would like to automate.

Say I have two tables, one has some first names, the second table has last names. I would like to cross join these two tables to get all possible combinations of first and last names. And then I would like to call my procedure for every row of the joined table.

CREATE TABLE #FN 
(
    FirstName varchar(255),
);

CREATE TABLE #LN 
(
    LastName varchar(255),
);

INSERT INTO #FN VALUES ('Augustinus'), ('Blepharo'), ('Gripus')

INSERT INTO #LN VALUES ('Sylla'), ('Petreius'), ('Cerinthus')

SELECT * 
INTO #NAMES 
FROM #FN 
CROSS JOIN #LN

And now I need to automate the following code, by going row for row from #NAMES, instead of manually typing the arguments.

CREATE TABLE #RESULT (Val VARCHAR(10))

INSERT INTO #RESULT
    EXEC('EXEC #myprocedure @arg1=''Augustinus'', @arg2=''Sylla'';
          EXEC #myprocedure @arg1=''Augustinus'', @arg2=''Petreius'';
          ...')

@CaiusJard Not a one time thing, to be called arbitrarily many times in the future. — user2974951, Sep 27 '19 at 13:09
Your query is essentially a duplicate of this one - https://stackoverflow.com/questions/886293/how-do-i-execute-a-stored-procedure-once-for-each-row-returned-by-query - take a read of it and see if you get stuck converting its advice for your situation? — Caius Jard, Sep 27 '19 at 13:11
Indeed, though there are ways of fiddling around not actually mentioning the word “cursor” such as selecting top row from temp table into variables, exec procedure, deleting row from temp, repeat til table empty.. but it’s a bit of a dirty way to simulate what you’d need a cursor for, just to appease some “thou shalt never utter the word cursor” dba:) — Caius Jard, Sep 27 '19 at 13:15
If not, is there a different / better / simpler way of achieving this? — user2974951, Sep 27 '19 at 13:16

score 0 · Answer 1 · answered Sep 27 '19 at 13:15

You can use select query to generate your dynamic execute stored procedure record by record.

Please check below code for your answer.

CREATE TABLE #FN (
    FirstName varchar(255),
);
CREATE TABLE #LN (
    LastName varchar(255),
);
INSERT INTO #FN VALUES ('Augustinus'),('Blepharo'),('Gripus')
INSERT INTO #LN VALUES ('Sylla'),('Petreius'),('Cerinthus')

SELECT * INTO #NAMES FROM #FN CROSS JOIN #LN

SELECT * FROM #NAMES

CREATE TABLE #RESULT (Val VARCHAR(1000))

INSERT INTO #RESULT(Val)
SELECT
    'EXEC #myprocedure @arg1=''' + N.FirstName + ''', @arg2=''' + N.LastName + ''';' AS [Value]
FROM #NAMES N;

SELECT Val FROM #RESULT;

DROP TABLE #FN;
DROP TABLE #LN;
DROP TABLE #NAMES;
DROP TABLE #RESULT;

Ermmm.. so where does this actually execute the procedure? And what about when it processes `Shamus O'Leary`? Dynamic sql can be parameterised too, it’s not injection resistant just because it’s in a stored procedure... — Caius Jard, Sep 27 '19 at 13:16

SQL Server : calling procedure iteratively

1 Answers1