sp_kickoff_dependent_jobs - https://saldeloera.wordpress.com/2012/01/31/metadata-driven-etl-dependent-job-execution/

sp_kickoff_dependent_jobs.sql

/* =======================================================================
Author:       Sal De Loera
Create date:  1/31/2012
Description:  Kicks off available SQL Agent Jobs flagged as ‘Ready’ in dbo.ETL_OBJ.ETL_STATUS_ID
Input:        N/A
Output:       execute applicable job
========================================================================= */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Sp_kickoff_dependent_jobs]
AS
DECLARE @execString AS VARCHAR(128);
–get distinct ETL_OBJ list where status = ‘Ready’
DECLARE MY_CURSOR CURSOR FOR
SELECT DISTINCT SQLAGENT_JOBNAME
FROM   dbo.T_ETL_OBJ AS teo WITH (NOLOCK)
INNER JOIN dbo.T_ETL_STATUS tes WITH (NOLOCK)
ON tes.ETL_STATUS_ID = teo.ETL_STATUS_ID
WHERE  tes.ETL_STATUS_DESC IN ( ‘Ready’ )
AND teo.IS_ENABLED_IND = ‘True’
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @execString
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
SET @execString = ‘msdb.dbo.sp_start_job N’ + ”” + @execString + ””
EXEC (@execString)
–END LOOP
FETCH NEXT FROM MY_CURSOR INTO @execString
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR