I have created a stored procedure which I would like to call in a SQL Server Agent job.
But the problem is that I want to pass dynamic values to the parameters of that stored procedure.
I am passing BankID and SiteID to the stored procedure. There are multiple banks and each bank is linked to multiple sites.
I want to pass multiple BankIDs, and multiple SiteIDs of each bank to the procedure in the SQL Server Agent job which will be running the procedure on a regular basis.
The stored procedure is basically adding and updating rows to a existing table on the same database.
I want to get the bankId using a select statement from the Banks table, and SiteID using another select statement from the Sites table.
Should I be creating the variables BankId and SiteID in different steps in the SQL Server Agent job? But I don't know how to do this. Is there another way to pass dynamic values to the stored procedure's parameters? Should I be using cursors in the SQL Server Agent job or should I be using the cursors in the stored procedure?
Example of a call:
Exec Shutdown_Periods BankID, SiteID
Instead of looping in a job more appropriate is use of handing over a table of values instead of single value for parameters.
Having an appropriate type for BankID and SiteID (aka table definition) there are multiple ways to load data into these "lists". Then hand over to the procedure.
Inside the procedure then handle processing as business logic describes. If it is one by one, then a forward only cursor to scroll through the table type is good enough.
If all can be done at once, then yes, use a simple update statement on related tables. If multiple tables are involved, use transactions to allow full success or no success at all.