[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 28787
  • Last Modified:

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
0
YLaw
Asked:
YLaw
  • 6
  • 5
1 Solution
 
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
 
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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
 
gjutrasCommented:
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now