Execute Proc in Variable

  • bozo7

    Ten Centuries

    Points: 1278

    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

  • Jeremy Kemp

    SSCertifiable

    Points: 6754

    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

  • bozo7

    Ten Centuries

    Points: 1278

    I got it to work! Just had to read more in BOL and do more figuring.

    Thanks,

    Ross

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply