Search code examples
sqlssissql-agent-jobsql-agent

Start SQL Server Agent job when case when statement returns true

I want to create a procedure that constantly checks and compares row counts between source and target table. If source table has a higher row count then I want to execute a SQL Server Agent job and my procedure should wait till that job finishes.

For Example:

create proc 'XYZ'
    case when a.count(*) > b.count(*) then sp_start_job 'SSIS_package_ABC' 

    wait for 'package execution completion'

I would really appreciate it if someone could point me in the right direction as I am new to SQL Server Agent.

Solution

  • Use IF statements instead of CASE:

    DECLARE @SRC_TABLE_CNT INT,
    @DEST_TABLE_CNT INT

SELECT @SRC_TABLE_CNT = COUNT(*) FROM SOURCE_TABLE

SELECT @DEST_TABLE_CNT = COUNT(*) FROM DEST_TABLE

IF @SRC_TABLE_CNT > @DEST_TABLE_CNT
BEGIN
    sp_start_job 'SSIS_package_ABC'
END