SQL - Execute stored procedure for all values in a table

Question

I have a SQL stored procedure 'A' which validates certain bank account information for a given account and it accepts the account number as an argument 'arg1'

I want to execute the procedure for all values present in Column X of another table XXX (all bank Accounts present in the Accounts table)

I am not sure if something like this would work

exec A @arg1 = X from XXX

Thanks in advance!

What is the purpose of "A"? It sounds like you are attempting to cursor which is generally a no no. Can you give more detail of what your pre/post data is/should be? — Bmo, Oct 31 '12 at 16:23
http://stackoverflow.com/questions/1656804/sql-call-stored-procedure-for-each-row-without-using-a-cursor — jazzytomato, Oct 31 '12 at 16:23
See if the TVF+CROSS APPLY helps http://stackoverflow.com/questions/477064/is-it-possible-to-execute-a-stored-procedure-over-a-set-without-using-a-cursor — Alex K., Oct 31 '12 at 16:25

score 11 · Accepted Answer · answered Oct 31 '12 at 16:22

No there isn't a bulk EXEC the way you want to run it.

Option 1: Generate and run by hand. Copy result, paste back into SSMS and execute.

select 'exec A @arg1 = ' + quotename(X,'''') + ';'
from XXX

Option 2: Generate a batch and run using dynamic SQL.

declare @sql nvarchar(max);
set @sql = '';
select @sql = @sql + 'exec A @arg1 = ' + quotename(X,'''') + ';'
from XXX;
exec (@sql);

Option 3: Run it in a loop

declare @x varchar(max);
select top(1) @x = X from xxx where X is not null order by X;
while @@rowcount > 0
begin
    exec sp_executesql N'exec A @arg1=@x;', N'@x varchar(max)', @x=@x;
    select top(1) @x = X from xxx where X > @x order by X;
end;

Thanks, the loop solution does what I am looking for – dopplesoldner Oct 31 '12 at 16:35 — dopplesoldner, Oct 31 '12 at 16:35

score 2 · Answer 2 · answered Oct 31 '12 at 16:27

2

It's generally better to approach things in a set-based way, but if you really do need to do something sequentially for each row in a result set, here's how you can use a cursor:

declare cur cursor for
select X from XXX

declare @x int
open cur
fetch next from cur into @x

while @@FETCH_STATUS = 0
BEGIN
    exec A @x

    fetch next from cur into @x
END

answered Oct 31 '12 at 16:27

Chad

7,279
2
24
34

I need to do the same thing: run a stored proc on a value from every row in a table. what is a way to think about that in a set-based way? – bernie2436 Sep 09 '13 at 16:06
@akh2103 It really depends on what your sproc does. You may be able to rewrite it to be set based with a correlated update, delete, or insert. If it has variables, replace those with table variables. Or it might not be feasible or worth the effort. – Chad Sep 09 '13 at 17:01

score 0 · Answer 3 · answered May 25 '18 at 22:02

Try using the dynamic query with Coalesce command. Below query executes A stored procedure on data of X column of XXX table.

-- Create Dynamic Query
DECLARE @ValidateAccountCommand VARCHAR(MAX) 
SELECT @ValidateAccountCommand = COALESCE(@ValidateAccountCommand + 
                                            '; EXEC A ', 
                                             'EXEC A ') 
                                + CONVERT(NVARCHAR,XxxData.X)
FROM 
(SELECT X FROM XXX) XxxData

PRINT @ValidateAccountCommand
EXECUTE (@ValidateAccountCommand)

SQL - Execute stored procedure for all values in a table

3 Answers3