CODEWITHSUNDEEP
asp.netsqldatabasestored-procedures
pancake
pancake

Reputation: 600

Run a Stored Procedure multiple times dynamically

I have created a stored procedure that calculates from some table and retrieve a new dataset back:

  " DECLARE @maxVal int " +
  " Set @maxVal = (SELECT ID FROM TableCustomers " +
  " WHERE Service_ID = @Service_ID) " +
  " execute SP_CaculateData @maxVal ";

Now the TableCustomers also have a column called CustomerName and each CustmerName can have multiple Service_ID's. How can I run my stored procedure multiple times, all depends on how many services each CustomerName has. Something like:

 execute SP_CaculateData @maxVal
 execute SP_CaculateData @maxVal
 execute SP_CaculateData @maxVal
 execute SP_CaculateData @maxVal

I have been reading something about cursors, but if anyone can give me a hand hear I would appreciate that.

Upvotes: 1

Views: 1382

Answers (1)

agileMike
agileMike

Reputation: 463

One option is to use a while loop to iterate through the customers and service ids:

declare
        @maxVal int
       ,@customerName varchar(200)
       ,@serviceID int

select @customerName = MIN(CustomerName)
from TableCustomers t

while(select COUNT(1)
      from TableCustomers t
      where t.CustomerName >= @customerName) > 0
    begin

        --here we are dealing w/ a specific customer
        --loop through the serviceIDs

        select @serviceID = MIN(Service_ID)
        from TableCustomers t
        where t.CustomerName = @customerName


        while(select COUNT(1)
              from TableCustomers t
              where t.CustomerName = @customerName
                and t.Service_ID >= @serviceID) > 0

            begin
                select @maxVal = MAX(Id)
                from TableCustomers t
                where t.Service_ID = @serviceID

                execute SP_CalculateData @maxVal

                select @serviceID = MIN(Service_ID)
                from TableCustomers t
                where t.CustomerName = @customerName
                  and t.Service_ID > @serviceID
            end


        select @customerName = MIN(CustomerName)
        from TableCustomers t
        where t.CustomerName > @customerName

    end

I can't say whether or not this is a solution that will perform better than a cursor, but it should get the job done.

Upvotes: 2

Related Questions