Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2002
    Location
    Cincinnati
    Posts
    4

    Exclamation Unanswered: Urgent-Conditonal Select help needed!

    I am trying to figure out what is wrong with the following statement. I have gone over it several times. Does anyone have an idea of what is wrong with it?


    use lprepmaster
    IF
    (Select * from tbl_MecStateTest where EMC_Complete ='yes')

    Begin
    print 'ILC portion of MEC has been completed'

    USE msdb
    EXEC sp_start_job @job_name = 'job_MonthlyTransfer'
    end

    ELSE
    Begin
    use lprepmaster
    print 'ILC Portion of Mec has not been completed'
    End

    Any help is appreciated!
    Thanks,
    melissa

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    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
    Code:
    use lprepmaster 
    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' 
    end 
    ELSE Begin 
        print 'ILC Portion of Mec has not been completed' 
    End

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •