Sorry if this is confusing: I will try to be as specific as possible.
I am passing in @Detail- it can be blank, null, or have text
If @Detail is not null or blank, I need to check if AppraisalResponse.Detail is blank or null (- I think this is where I am running into so much trouble). If it is blank or null then I need to check if there is already a record in AppraisalHistory with this ID. If there is, I need to insert the blank record to history, otherwise not.
If @Detail is null. I need to check if there is already a record in Appraisal History with this ID. If there is, I need to insert the blank record to history, otherwise not.
IF @Detail is not null
AND (SELECT
COUNT(AppraisalResponseID)
FROM AppraisalResponseHistory
WHERE AppraisalResponseID = @AppraisalResponseID) >0
BEGIN
INSERT INTO AppraisalResponseHistory
( AppraisalResponseID,
PermissionID,
AppraisalTypeQuestionPromp
tID,
Detail,
ModifiedDate,
PersonID )
SELECT AppraisalResponse.ID,
AppraisalResponse.Permissi
onID,
AppraisalResponse.Appraisa
lTypeQuest
ionPromptI
D,
AppraisalResponse.Detail,
AppraisalResponse.Modified
Date,
PersonID
FROM AppraisalResponse
WHERE AppraisalResponse.ID=@Appr
aisalRespo
nseID AND AppraisalResponse.Detail<>
@Detail
END
ELSE
IF COALESCE(@Detail, '') = ''
--if the reviewer is inserting a blank record after having filled in a response, insert into history
--if the blank record is the place holder, do not insert into history
AND (SELECT
COUNT(AppraisalResponseID)
FROM AppraisalResponseHistory
WHERE AppraisalResponseID = @AppraisalResponseID) >0
BEGIN
INSERT INTO AppraisalResponseHistory
( AppraisalResponseID,
PermissionID,
AppraisalTypeQuestionPromp
tID,
Detail,
ModifiedDate,
PersonID )
SELECT AppraisalResponse.ID,
AppraisalResponse.Permissi
onID,
AppraisalResponse.Appraisa
lTypeQuest
ionPromptI
D,
AppraisalResponse.Detail,
AppraisalResponse.Modified
Date,
PersonID
FROM AppraisalResponse
WHERE AppraisalResponse.ID=@Appr
aisalRespo
nseID AND AppraisalResponse.Detail<>
@Detail
END