I just did a small test and it looks like you will need to add 'EXISTS' to your IF statement, since the IF statement is testing a condition and can not handle records coming back. Use the EXISTS, if you are test that atleast one record meets this criteria.
IF EXISTS (Select * from tbl_MecStateTest where EMC_Complete ='yes')
If you are testing for a particular amount of records that meet this criteria then use a COUNT(*)
IF (Select COUNT(*) from tbl_MecStateTest where EMC_Complete ='yes') > 100
You can remove the 'USE' and call the procedure by its fullname
IF EXISTS (Select * from tbl_MecStateTest where EMC_Complete ='yes') Begin
print 'ILC portion of MEC has been completed'
EXEC msdb..sp_start_job @job_name = 'job_MonthlyTransfer'
print 'ILC Portion of Mec has not been completed'