SQL Need to check if null or empty string and another condition - query not working

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,
                  AppraisalTypeQuestionPromptID,
                  Detail,
                  ModifiedDate,
                  PersonID )
      
            SELECT AppraisalResponse.ID,
                  AppraisalResponse.PermissionID,
                   AppraisalResponse.AppraisalTypeQuestionPromptID,
                   AppraisalResponse.Detail,
                  AppraisalResponse.ModifiedDate,
                  PersonID
            FROM AppraisalResponse
            WHERE AppraisalResponse.ID=@AppraisalResponseID 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,
                  AppraisalTypeQuestionPromptID,
                  Detail,
                  ModifiedDate,
                  PersonID )
      
            SELECT AppraisalResponse.ID,
                  AppraisalResponse.PermissionID,
                   AppraisalResponse.AppraisalTypeQuestionPromptID,
                   AppraisalResponse.Detail,
                  AppraisalResponse.ModifiedDate,
                  PersonID
            FROM AppraisalResponse
            WHERE AppraisalResponse.ID=@AppraisalResponseID AND AppraisalResponse.Detail<>@Detail

END
YLawAsked:
Who is Participating?
 
gjutrasConnect With a Mentor Commented:
the select statement it's part of doesn't reference AppraisalResponse at all, it only references AppraisalResponseHistory.  You can prepend AppraisalResponseHistory. in front of detail but that will be the same.

Detail is null or LEN(Detail) > 0 is checking the wrong thing  it should be
Detail is null or LEN(Detail) = 0 -- null or length of 0
0
 
gjutrasCommented:
-- need empty history record if detail is null and it doesn't exist yet
IF @Detail is null OR LEN(@Detail) = 0
      AND (SELECT
            COUNT(AppraisalResponseID)
            FROM AppraisalResponseHistory
            WHERE AppraisalResponseID = @AppraisalResponseID) =0
      BEGIN
            INSERT INTO AppraisalResponseHistory
            ( AppraisalResponseID,
                  PermissionID,
                  AppraisalTypeQuestionPromptID,
                  Detail,
                  ModifiedDate,
                  PersonID )
                  values(@AppraisalResponseID,null,null,'',GETDATE(),null)      
     
      END

-- if detail isn't null or blank and Appraisal detail is null or blank and an id exists, insert blank record
      IF @Detail is not null AND LEN(@Detail) > 0
      AND (SELECT
            COUNT(AppraisalResponseID)
            FROM AppraisalResponseHistory
            WHERE AppraisalResponseID = @AppraisalResponseID
            and (AppraisalResponse.Detail is null OR LEN(AppraisalResponse.Detail) > 0)) > 0
             BEGIN
            INSERT INTO AppraisalResponseHistory
            ( AppraisalResponseID,
                  PermissionID,
                  AppraisalTypeQuestionPromptID,
                  Detail,
                  ModifiedDate,
                  PersonID )
                  values(@AppraisalResponseID,null,null,'',GETDATE(),null)      
     
      END

0
 
YLawAuthor Commented:
sorry maybe I wasn't clear.  I don't want null values in the history table unless it's at least the second record in histroy.  Meaning they had inserted text previously and then blanked it out.  Am I understanding correctly that your first one inserts null values if there is no record and they inserted a null value.  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 I don't want a blank record in History.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
gjutrasCommented:
I reveresed them from your original.  The 2nd one takes care of that.
if detail has something and an appraisal id exists but with null or blank detail insert a blank record.

the top one is if detail is empty or null and an id exists insert a blank.
0
 
gjutrasCommented:
sorry, typo, change the top one
WHERE AppraisalResponseID = @AppraisalResponseID) =0
shoudl be
WHERE AppraisalResponseID = @AppraisalResponseID) >0
0
 
YLawAuthor Commented:
now I see the confusion.  Basically, what I am doing is copying whats in the Appraisal Table to the History Table.  I start out with a blank record in the table.  I only want to copy that blank record to the history table if it is intentional, meaning that someone had text and then deleted it.  

So if there is a record in history already (meaning they had text in previously) copy the blank record that's in the Appraisal table to History.  So the insert should still be

SELECT AppraisalResponse.ID,
                  AppraisalResponse.PermissionID,
                   AppraisalResponse.AppraisalTypeQuestionPromptID,
                   AppraisalResponse.Detail,
                  AppraisalResponse.ModifiedDate,
                  PersonID
            FROM AppraisalResponse
            WHERE AppraisalResponse.ID=@AppraisalResponseID AND AppraisalResponse.Detail<>@Detail

becuase we are not inserting unless there is a difference between what's in there now and the new @Detail (which might be blank).

Does this make it more clear?
0
 
gjutrasCommented:
How's this?  I put them back in your original order.

IF @Detail is not null AND LEN(@Detail) > 0
      AND (SELECT
            COUNT(AppraisalResponseID)
            FROM AppraisalResponseHistory
            WHERE AppraisalResponseID = @AppraisalResponseID AND (Detail is null or LEN(Detail) > 0)) >0
      BEGIN
            INSERT INTO AppraisalResponseHistory
            ( AppraisalResponseID,
                  PermissionID,
                  AppraisalTypeQuestionPromptID,
                  Detail,
                  ModifiedDate,
                  PersonID )
     
            SELECT AppraisalResponse.ID,
                  AppraisalResponse.PermissionID,
                   AppraisalResponse.AppraisalTypeQuestionPromptID,
                   AppraisalResponse.Detail,
                  AppraisalResponse.ModifiedDate,
                  PersonID
            FROM AppraisalResponse
            WHERE AppraisalResponse.ID=@AppraisalResponseID AND AppraisalResponse.Detail<>@Detail
     
      END

IF @Detail is null OR LEN(@Detail) = 0
      AND (SELECT
            COUNT(AppraisalResponseID)
            FROM AppraisalResponseHistory
            WHERE AppraisalResponseID = @AppraisalResponseID) >0
      BEGIN
            INSERT INTO AppraisalResponseHistory
            ( AppraisalResponseID,
                  PermissionID,
                  AppraisalTypeQuestionPromptID,
                  Detail,
                  ModifiedDate,
                  PersonID )
     
            SELECT AppraisalResponse.ID,
                  AppraisalResponse.PermissionID,
                   AppraisalResponse.AppraisalTypeQuestionPromptID,
                   AppraisalResponse.Detail,
                  AppraisalResponse.ModifiedDate,
                  PersonID
            FROM AppraisalResponse
            WHERE AppraisalResponse.ID=@AppraisalResponseID AND AppraisalResponse.Detail<>@Detail

END
0
 
YLawAuthor Commented:
thank that looks good.  But, I just want to double check.  The first one

IF @Detail is not null AND LEN(@Detail) > 0
      AND (SELECT
            COUNT(AppraisalResponseID)
            FROM AppraisalResponseHistory
            WHERE AppraisalResponseID = @AppraisalResponseID AND (Detail is null or LEN(Detail) > 0)) >0

this says @detail has some sort of string and there is a corresponding record already in Appraisal response history, then it should do the history update.  Why do we need the AND (Detail is null or LEN(Detail) > 0)) >0.  
0
 
gjutrasCommented:
that's to satisfy your statement in the original post
"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"
0
 
YLawAuthor Commented:
so, Detail is checking AppraisalResponse.Detail not blank.  Right, I need to check if it's null and then only do it if the Count in History is GT 0.  So does that do that?  Thanks so much.
0
 
YLawAuthor Commented:
it looks like it's looking at History.Detail and not AppraisalResponse.Detail to check if the last record is blank.  I specifically need it to look at the other table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.