SQL Job dependency

Hi, I wanted to create a process to control SQL Server Jobs that depend on each other. Job Control Process The process controls SQL Server Jobs that depend on each other ie. if Job1 depends on Job 2 ie. Job2 has to complete, before Job1 can start, then the process will control the job starting. At the moment the jobs are scheduled to run at specific times eg. Job2 is scheduled to start at 1pm and Job1 at 2pm. The issue is that if Job2 runs for over an hour, the next job (Job1) would fail. The process controls job start times based on Job levels (not Job steps). The following tables in the Operations database manage the control process. • AS_JobSchedules - A table that contains the jobs • AS_Dependencies - A table that contains a list of Jobs and their dependencies. ? JobID FK to JSID in JobSchedules, ? JobDependencyID FK to JSID in JobSchedules ? Priority specifies priority of dependency in case 1 job depends on more than one job. • AS_JobRunLog - A table that logs RunStartdate, RunCompletedDate or RunFailedDate of every job that runs. • AS_JobRunWaitLog - A table that logs the job that’s waiting, the job that’s causing the wait and duration of wait. Every Job will have 2 extra steps around the normal job steps of the job. 1. Begin Check Job dependency 2. Whatever the job steps are... 3. Log Job dependency The broad process might be: 1. Job1 begins – Step1 (Begin Check Job dependency stored Procedure) a. INSERTs a record in the AS_JobRunLog table with RunStartDate (leaving RunCompletedDate and RunFailedDate as NULL) b. Checks AS_Dependencies table for its dependency job/s. It then checks to see if that job's entry in the AS_JobRunLog table has a RunCompleted that is not null and within 24 hours of the RunStartDate of this job (ie the job that ran last actually completed). c. If it’s completed, the Steps 1 completes successfully and the next step can begin. d. If not; ie the job is still running, it INSERTs a record in the AS_JobRunWaitLog. e. It uses a WAITFOR and hangs back for 10 minutes and tries again until it finds the dependency job has completed. The job step then completes successfully and the next step can begin. 2. Step2 – The actual step of that job. 3. Step 3 - (Log Job dependency stored Procedure) a. If the previous job step succeeds, Step 3 stored procedure UPDATEs the record in the AS_JobRunLog table with RunCompletedDate (leaving RunFailedDate as NULL) b. If the previous job step fails, Step 3 stored procedure UPDATEs the record in the AS_JobRunLog table with RunFailedDate (leaving RunCompletedDate as NULL) So in summary, I need someone to write 2 stored procedures that achieve the above process using the tables listed. 1. usp_BeginCheckJobDependency stored Procedure 2. usp_LogJobDependency stored Procedure Please quote for this. Thank you.