Execute Proc in Variable
-
I am wanting to execute a list of stored procedures stored in a table. They all have the same parameters. Here is my code thus far:
declare @TempID int, @InqTotal smallint, @TotalReturn smallint,
@CalcReturn decimal(3,1);
declare @RowName varchar(50), @GroupID smallint, @OrderID smallint,
@SPName varchar(40), @InqTotal bit, @AsstTotal bit;
declare ExecTemp_Cursor Static Cursor for
select * from CCR_ExecTemplate
order by GroupID, OrderID;
Open ExecTemp_Cursor;
FETCH NEXT FROM ExecTemp_Cursor into
@RowName, @GroupID, @OrderID, @SPName, @InqTotal, @AsstTotal;
while @@FETCH_STATUS = 0
begin
insert into #CCR_Exec;
set @TempID= @@Identity;
--I want to do something like this @SPName comes from a field in a table with all the parameters required
exec @SPName @P1Begin, @P1End, @InqTotal, @TotalReturn, @CalcReturn;
end
#CCR_Exec is a temp table for a report.
Is what I am trying to do possible? Or do I need to code something like this on the client side?
Thanks,
Ross
-
I've not done exactly what you are trying to do but I have done something very similar to exec @SPName @P1Begin, @P1End, @InqTotal, @TotalReturn, @CalcReturn.
In my application, the users request a report which goes through a common proc for validation which then calls another proc depeneding on which report they selected (I get the value from a look up table). I exec the second proc using a variable name.
Have you tried coding this? If not try it - if you have problems come back and see if anyone can sort them out.
Jeremy
-
I got it to work! Just had to read more in BOL and do more figuring.
Thanks,
Ross
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply