SQL - Execute stored procedure for all values in a table
sql, sql-server, stored-procedures
Solution
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;
Problem
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!