How to update status for part with nothing data when this part is missed?

I work with SQL Server 2012 and have an issue: I can’t update status with No data returned where no result returned from select statement cross apply function.

Meaning where no data returned when join parts and company to function then update status to this part with nothing data for that part when data is missing.

create table #TempPC   
(  
    PartNumber NVARCHAR(300),  
    CompanyId INT,  
    Status NVARCHAR(200)  
)  

INSERT INTO #TempPC (PartNumber, CompanyId)   
VALUES ('9C06031A2R43FKHFT', 1233345),  
       ('VJ0805AIR5CXAMT', 8433324)

When I run a select as shown below, it does not return data for some parts so I will update status with nothing data returned to this part:

SELECT 
    t.PartNumber, t.CompanyName, pc.FamilyName, t.Status 
FROM  
    #TempPC t    
CROSS APPLY
    [PC].FN_PartCheck_Test (t.[PartNumber], 0, 1, 1, t.CompanyId) pc     
WHERE 
    pc.GroupID > -2 AND pc.PortionID > -2

What I need to do when any parts and company on temp table join with function is missed, then update status to this part with nothing data for this part .

Expected result:

PartNumber            CompanyId     status  
---------------------------------------------------------
9C06031A2R43FKHFT     1233345       No data returned  
VJ0805AIR5CXAMT       8433324       it have data returned

How to do that please ?

SELECT * 
FROM [PC].FN_PartCheck_Test (t.[PartNumber], 0, 1, 1, t.CompanyId) pc  

RETURN ID PartNumber, CompanyID, FamilyID

Answer

If I understand correctly, you want outer apply, but you also need a subquery:

Select t.PartNumber, t.CompanyName, pc.FamilyName, t.Status
from  #TempPC t outer apply 
      (select pc.*
       from [PC].FN_PartCheck_Test( t.[PartNumber], 0, 1, 1, t.CompanyId) pc     
       where pc.GroupID > -2 And pc.PortionID > -2 
      ) pc ;

Advertisement

Answer