CREATE PROCEDURE dbo.FollowupUpdateState @AFollowupNumber INT, @AUserIdNumber INT, @ANewState TINYINT, @ADeviation BIT = 0, @ANote VARCHAR(8000) = '' AS
/*
  Created by Kent Johansen May 2009
  Notes: 
    Update state on followup. If @Note <> '' then it is added to the existing note.
    @ANewState = TFollowUpState = (fusNone, fusNew, fusChanged, fusDone, fusCanceledDeposits, fusCanclled);

  Change log:
    KJ 20091102: Added Carige return between original note and the new note.
    KJ 20090722: Added Raiseerror if @ANewState has an invalid value

*/
IF (@ANewState < 0) OR (@ANewState > 5)
BEGIN
  DECLARE @s VARCHAR(1000)
  SET @s = 'Error in storedprocedure <<FollowupUpdateState>>: @ANewState needs to be between 0 and 5. Actually value is ' + CONVERT(VARCHAR, @ANewState) + '. '
  RAISERROR(@s,15,1)
END
ELSE
BEGIN
  IF @AFollowupNumber > 0
  BEGIN
    DECLARE @dt DATETIME
    SET @dt = CONVERT(DATETIME, (SELECT SystemDate FROM System WITH (NOLOCK) WHERE NUMBER = 1) + ' ' + CONVERT(CHAR(8), CURRENT_TIMESTAMP, 108))

    IF @ANote <> ''
    BEGIN
      SET @ANote = CHAR(13) + @ANote

      DECLARE @Ptr BINARY(16)
      DECLARE @n INT
 
      SELECT @Ptr = TEXTPTR(Note), @n = DATALENGTH(Note) FROM Followup WITH (NOLOCK) WHERE FollowupNumber = @AFollowupNumber

      UPDATETEXT Followup.Note @Ptr @n 0 @ANote
    END

    IF @ANewState = 3 --fudDone
    BEGIN
      UPDATE Followup SET State = @ANewState, CompletedBy = @AUserIdNumber, CompletedDate = @dt, Deviation = ISNULL(@ADeviation, 0)
      WHERE FollowupNumber = @AFollowupNumber
    END
    ELSE
    BEGIN
      UPDATE Followup SET State = @ANewState, Deviation = ISNULL(@ADeviation, 0)
      WHERE FollowupNumber = @AFollowupNumber
    END
  END
END