executing store procedures based on the store procedure select query results

Question

Hi,

I've got a situation where I need to execute a stored procedure based on the Location and report ID return values.

Here are my sp code and select query result. 

Declare  @TodayDate datetime, @NextRunDate datetime;
Set        @TodayDate   = convert(date, getdate())

Select   ReportID, Type, Location, NextRunDate, SpToExec Into #ReportSchedule From tblSchedule Where NextRunDate = @TodayDate

Select * From #ReportSchedule (This gives me the details of the two report stored procedures I need to run)

Next, I want to execute the stored procedures using the value returned in my SQL code. Ideally, I would like to do some sort of comparison before I execute the store procedure. This is to pass the sp parameter dynamically, instead of hard coded value.

As per above example, I want to execute following two sp that is for this report id and location. 

Exec spCR1005HL7NEW0717     @Location = N'Bracknell'
Exec spCR1006HL7NEW0717     @Location = N'Bracknell'

thank you in advance...!

---

Mustansar

Answer 1

You would schedule an agent job that would read your table.

When reading the table, it would check which procedures that are due to day:

DECLARE cur CURSOR STATIC LOCAL FOR
   SELECT spname, par FROM tbl
   WHERE  nextrundate = convert(date, getdate())

OPEN cur

WHILE 1 = 1
BEGIN    FETCH cur INTO @spname, @par
   IF @@fetch_status <> 0
      BREAK

   EXEC @spname @par
END
DEALLOCATE cur

For things to be this simple, all procedure must have the same parameter profile. That could be one, two or more parameters, but all procedures must accept the number you pass.

Note that this assumes that you store procedure name and parameter value separately.

In case it is not clear: EXEC accepts a variable name for the procedure to call.

Answer 2

Thanks, Xi

This was very helpful...!

---

Mustansar