Puzzling If statements results in TSQL code

[hedgracer]

I have the following TSQL if statments:

IF @sExch = '01' AND @MonthCurr = 1
delete from CBOTPrelim where CBOTPrelim.FirmNumber = @sFirm and CBOTPrelim.YearMonth = @ymprev
else
delete from CBOTPrelim where CBOTPrelim.FirmNumber = @sFirm and CBOTPrelim.YearMonth = @ym

IF @sExch = '02' AND @MonthCurr = 1
delete from CMEPrelim where CMEPrelim.FirmNumber = @sFirm and CMEPrelim.YearMonth = @ymprev
else
delete from CMEPrelim where CMEPrelim.FirmNumber = @sFirm and CMEPrelim.YearMonth = @ym

when I import a file with a @sFirm of 177 and @sExch of 01 the data for the firm and exch is erased from both the CBOTPrelim table and the CMEPrelim table and entered into the CBOTPrelim table. If I then import firm 177 and exch of 02 the above code then erases the information in CBOTPrelim and CMEPrelim table and enters the information into the CMEPrelim table. I just want the information deleted from the CBOTPrelim table if the exch is 01 or the CMEPrelim table if the exch is 02. What am I doing wrong that I am not seeing here? Thanks for all help in advance.

Dave

 

[genomon]

Have you looked at the table designs to see if there are triggers behaving badly?

"Time flies like an arrow; fruit flies like a banana."

 

[bborissov]

But you delete from BOTH tables no matter what is @sExch.
Maybe, just maybe because I am not sure I understand your question completely, you need this:

IF @sExch = '01'
  BEGIN
     IF @MonthCurr = 1
        delete from CBOTPrelim 
               where CBOTPrelim.FirmNumber = @sFirm and
                     CBOTPrelim.YearMonth = @ymprev
     else
        delete from CBOTPrelim
               where CBOTPrelim.FirmNumber = @sFirm and
                     CBOTPrelim.YearMonth = @ym
  END

IF @sExch = '02'
   BEGIN
       IF @MonthCurr = 1
          delete from CMEPrelim
                 where CMEPrelim.FirmNumber = @sFirm and
                       CMEPrelim.YearMonth = @ymprev
       else
          delete from CMEPrelim
                 where CMEPrelim.FirmNumber = @sFirm and
                       CMEPrelim.YearMonth = @ym
   END

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[JackVam]

The issue is how you use the If statement , the ELSE statement needs to be investigated

If exch = 01
delete from CBOTPrelim where CBOTPrelim.FirmNumber = @sFirm and CBOTPrelim.YearMonth = @ymprev

If exch = 02
delete from CMEPrelim where CMEPrelim.FirmNumber = @sFirm and CMEPrelim.YearMonth = @ymprev


Jack Vamvas

All the IT jobs in one place -

http://www.ITjobfeed.com