SQL Stored Procedure not returning any data

Question

When I run a simple SELECT statement on this table I get a row back (this is correct there should only be one). Here is that SELECT statement:

select * from Lending.HMDA where BatchId = 1

Here is the proc I am executing, which returns no rows:

    DECLARE @BatchStartDate datetime, @BatchEndDate datetime

    SELECT @BatchStartDate = StartDate, @BatchEndDate = EndDate  FROM Lending.HMDAReportBatch WHERE BatchId = 1

    -- HMDA And App Data
    SELECT
    A.ApplicationId,
    A.CreatedDate,
    LU14.LookupCode AS LoanTypeId,
    LU1.LookupCode AS PropertyTypeId,
    LU2.LookupCode AS LoanPurposeId,
    LU3.LookupCode AS OwnerOccupancyId,
    L.FinalLoanAmount,
    LU4.LookupCode AS PreApprovalId,
    LU5.LookupCode AS ActionId,
    A.ActionDate,
    H.MSA,
    LU6.MiscCode1 AS StateId,
    LU7.LookupCode AS CountyId,
    H.CensusTract,
    LU8.LookupCode AS ApplicantEthnicityId,
    LU9.LookupCode AS JointEthnicityId,
    H.IsApplicantRaceAmericanIndian,
    H.IsApplicantRaceAsian,
    H.IsApplicantRaceBlack,
    H.IsApplicantRaceIslander,
    H.IsApplicantRaceNA,
    H.IsApplicantRaceNotProvided,
    H.IsApplicantRaceWhite,
    H.IsJointRaceAmericanIndian,
    H.IsJointRaceAsian,
    H.IsJointRaceBlack,
    H.IsJointRaceIslander,
    H.IsJointRaceNA,
    H.IsJointRaceNotProvided,
    H.IsJointRaceWhite,
    LU10.LookupCode AS ApplicantGenderId,
    LU11.LookupCode AS JointGenderId,
    LU12.LookupCode AS LoanPurchaserId,
    H.IsDenialReasonCash,
    H.IsDenialReasonCollateral,
    H.IsDenialReasonCreditHistory,
    H.IsDenialReasonDTI,
    H.IsDenialReasonEmploymentHistory,
    H.IsDenialReasonIncomplete,
    H.IsDenialReasonInverifiableInfo,
    H.IsDenialReasonMortgageInsuranceDenied,
    H.IsDenialReasonOther,
    H.RateSpread,
    H.IsHOEPA,
    LU13.LookupCode AS LienStatusId
    --@BatchStartDate AS BatchStartDate,
    --@BatchEndDate AS BatchEndDate
    FROM Lending.HMDA H
    INNER JOIN Lending.Application A ON H.ApplicationId = A.ApplicationId
    INNER JOIN Lending.Loan L ON H.ApplicationId = L.ApplicationId
    INNER JOIN tblLookup AS LU1 ON H.PropertyTypeId = LU1.LookupID
    INNER JOIN tblLookup AS LU2 ON H.LoanPurposeId = LU2.LookupID
    INNER JOIN tblLookup AS LU3 ON H.OwnerOccupancyId = LU3.LookupID
    INNER JOIN tblLookup AS LU4 ON H.PreApprovalId = LU4.LookupID
    INNER JOIN tblLookup AS LU5 ON H.ActionId = LU5.LookupID
    INNER JOIN tblLookup AS LU6 ON H.StateId = LU6.LookupID
    INNER JOIN tblLookup AS LU7 ON H.CountyId = LU7.LookupID
    INNER JOIN tblLookup AS LU8 ON H.ApplicantEthnicityId = LU8.LookupID
    INNER JOIN tblLookup AS LU9 ON H.JointEthnicityId = LU9.LookupID
    INNER JOIN tblLookup AS LU10 ON H.ApplicantGenderId = LU10.LookupID
    INNER JOIN tblLookup AS LU11 ON H.JointGenderId = LU11.LookupID
    INNER JOIN tblLookup AS LU12 ON H.LoanPurchaserId = LU12.LookupID
    INNER JOIN tblLookup AS LU13 ON H.LienStatusId = LU13.LookupID
    INNER JOIN tblLookup AS LU14 ON H.LoanTypeId = LU14.LookupID

    WHERE H.BatchId = 1  AND H.IsExcluded <> 'True'

Why is the proc not returning any data? What could be the possible scenario in which this would happen?

Answer 1

An inner join only returns a row if the on condition matches. Try to change them all to left outer join and see if you get any rows.

In a left join, a table's columns will be null when the on condition fails to match. This is usually a quick way to find the offending table.

Answer 2

Obviously, your first query and second query are worlds apart. In the second query, with all those inner joins, you are requiring that a row in Lending.HDMA (and have IsExcluded <> 'True' which is a criteria that does not exist in the first query) and have a matching row in the other tables in the query as well. If you are looking for the table that is filtering out the results, I would comment all joins and one-by-one uncomment a join and execute the query to see if you get rows. (Obviously, you will need to temporarily change the Select clause to something like Select * to do this.)

Answer 3

Possible scenarios:

• No rows where H.BatchId = 1 AND H.IsExclueded <> 'True'
• No matching rows in table Application
• No matching rows in table Loan
• No matching rows in tblLookup for PropertyTypeId
• No matching rows in tblLookup for LoanPurposeId
• No matching rows in tblLookup for OwnderOccupancyId
• No matching rows in tblLookup for OreApprovalId
• No matching rows in tblLookup for ActionUId
• No matching rows in tblLookup for StateId
• No matching rows in tblLookup for CountyId
• No matching rows in tblLookup for ApplicantEthnicityId
• No matching rows in tblLookup for JointEthnicityId
• No matching rows in tblLookup for ApplicantGenderId
• No matching rows in tblLookup for JointGenderId
• No matching rows in tblLookup for LoadPurchaserId
• No matching rows in tblLookup for LienStatusId
• No matching rows in tblLookup for LoanTypeId

Answer 4

Are you positive that there's ever a case where H.BatchId =1 and H.IsExcluded <> 'True'? If not, that could do it.

Answer 5

Without having any knowledge of your database, it's hard to say. But I would suspect one of those Id joins is not returning a row. If you replace those joins with sub-queries it'd be easier to see which one is the culprit.

For example...

SELECT
    A.ApplicationId,
    A.CreatedDate,
    (SELECT LookupCode
         FROM tblLookup
         WHERE H.LoanTypeId = LookupCode) AS LoantypeId,
   ...etc...

Answer 6

A quick way of seeing where the row is disappearing might be to execute the query with the "Include Actual Execution Plan" option enabled and have a quick mouse over the arrows to see where the number of rows becomes zero.