Null value in where clause with two different parameters

There is the following structure in the database. l_family_id is always returned as a parameter, but l_account and l_product_id each time one of the 2 is null. How can this be overcome so that I don't get an error in the select Can be searched account or product_id.

              such as

            select product_id from tbl27 
                where family_id = l_family_id
                and account  = null
                and product_id = l_product_id
                 
               and   
                  
            select product_id from tbl27 
                where family_id = l_family_id
                and account  = l_product_id
                and product_id = null 


             
            family_id       family   account    product_id

               1            family1    101      10
               1            family1    101      20
               1            family1    103      30
               1            family1    103      40
               2            family2    201      110
               2            family2    201      120
               2            family2    204      130
               2            family2    204      140
            




 CREATE TABLE tbl27 (family_id int, family VARCHAR2(30), account int, product_id 
    int);

      insert into tbl27 (family_id,family,account, product_id)
      SELECT 1,  'family1', 101, 10    FROM DUAL UNION ALL
      SELECT 1,  'family1', 101, 20    FROM DUAL UNION ALL
      SELECT 1,  'family1', 103, 30    FROM DUAL UNION ALL
      SELECT 1,  'family1', 103, 40    FROM DUAL UNION ALL
      SELECT 2,  'family2', 201, 110   FROM DUAL UNION ALL
      SELECT 2,  'family2', 201, 120   FROM DUAL UNION ALL
      SELECT 2,  'family2', 204, 130   FROM DUAL UNION ALL
      SELECT 2,  'family2', 204, 140   FROM DUAL;


        Expected result;

            select product_id from tbl27 
            where family_id = 1
            and account  = 101
            and product_id = null 

            product_id
                10
                20

Solution

I think I get what you are asking. You would need to check for a null parameter or that the value matches.

--Load Data
CREATE TABLE #tmp_tbl27 (family_id int, family VARCHAR(30), account int, product_id int);

INSERT INTO #tmp_tbl27 (family_id, family, account, product_id)
    SELECT 1,  'family1', 101, 10    UNION ALL
    SELECT 1,  'family1', 101, 20    UNION ALL
    SELECT 1,  'family1', 103, 30    UNION ALL
    SELECT 1,  'family1', 103, 40    UNION ALL
    SELECT 2,  'family2', 201, 110   UNION ALL
    SELECT 2,  'family2', 201, 120   UNION ALL
    SELECT 2,  'family2', 204, 130   UNION ALL
    SELECT 2,  'family2', 204, 140   

-- Example        
DECLARE @familyId int = 1;
DECLARE @accountId int = 101;
DECLARE @productId int = null;

select product_id from #tmp_tbl27 
where family_id = @familyId
and (@accountId is null OR account = @accountId)
and (@productId is null OR product_id = @productId)