I have an input list of int in a stored procedure, every id has a document, and every document has multiple subdocuments. Connected on subdocument.DocumentId = document.DocumentId.

What I have to do is return a list of objects (document.DocumentId int, IsValid bool).

The bool is true if every connected subdocument's 2 columns are not null.

PARAMLIST: list of ids

SELECT 
    IDS.ParamList AS documentId,
    CASE 
        WHEN (SELECT COUNT(*) 
              FROM Document D
              JOIN SubDocument SD ON SD.DocumentId = D.DocumentId
              WHERE SD.DocumentId = IDS.ParamList 
                AND SD.PaymentDate IS NULL 
                AND SD.ConnectionContractIsAcceptedDate IS NULL) > 0
             THEN 1
             ELSE 0 
    END AS IsValid
FROM 
    @documentIds IDS

As you can see my logic was to make a case switch where I count every SubDocument which doesn't have at least one of the columns filled, but the query doesn't return anything just the 2 column names

Document table:

SubDocument table:

Here the expected result is:

CodePudding user response：

You can join the table variable to the tables.
Then use conditional aggregation to calculate IsValid.

declare @DocumentIds table (
 DocumentId int
);

insert into @DocumentIds values (1),(2);

SELECT Doc.DocumentId, Doc.CreatedBy
, CAST(MIN(
      CASE
      WHEN (SubDoc.Field1 IS NULL OR SubDoc.Field2 IS NULL) 
      THEN 0
      ELSE 1
      END) AS BIT) AS IsValid
FROM Document Doc
JOIN @DocumentIds Ids 
  ON Ids.DocumentId = Doc.DocumentId
LEFT JOIN SubDocument SubDoc
  ON SubDoc.DocumentId = Doc.DocumentId
GROUP BY Doc.DocumentId, Doc.CreatedBy
ORDER BY Doc.DocumentId;

Demo on db<>fiddle here