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!
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;
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
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)
