IF statement

• sql_prodigy

  SSCrazy

  Points: 2254

  More actions

  November 4, 2008 at 8:18 am

  #200930

  i've written a simple IF statement, have a look at the following code snippet...

  DECLARE

  @Result DATETIME

  SET

  @Result =

  (SELECT

  TOP (1) Time

  FROM

  PropertyCollection

  WHERE

  (Name = 'AccountMovement.LastTime')

  ORDER BY Time DESC)

  SELECT ISNULL(DATEDIFF(second,@Result,GETDATE()), 0)

  IF (@Result > 600) THEN

  BEGIN

  PRINT 'INSERTED'

  END

  ELSE

  PRINT 'NOT INSERTED'

  this checks whether the last inserted record was more than 10 minutes, if it was then insert else it won't be inserted.

  problem is, even if the @Result value was less than 10 minutes the PRINT 'INSERTED' block is still run...

  is this because the return datatype of datadiff is not a integer but a datetime value...

• Kev Riley

  SSCrazy Eights

  Points: 9076

  More actions

  November 4, 2008 at 8:36 am

  #893668

  yisaacs,

  the @Result parameter is holding the datetime from your query.

  You then select the number of seconds different from current time but don't affect @Result at all.

  Your IF is then using @Result in the logic check i.e. if @Result was '1 oct 2008 12:34:15', you are comparing '1 oct 2008 12:34:15' > 600.

  Either put the result of the datediff into another variable or use the whole datediff clause in the logic check

  IF (ISNULL(DATEDIFF(second,@Result,GETDATE()), 0) > 600) ......

  Kev