I work on SQL server 2012 I face 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 make select below it 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
so 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
so How to do that Please ?
select * from [PC].FN_PartCheck_Test( t.[PartNumber],0,1,1,t.CompanyId) pc
Return ID PartNumber,CompanyID,FamilyID
What I have tried:
Select t.PartNumber,t.CompanyName,pc.FamilyName,t.Status FROM #TempPC t
outer apply [PC].FN_PartCheck_Test( t.[PartNumber],0,1,1,t.CompanyId) pc
Where pc.GroupID>-2 And pc.PortionID>-2 and pc.FamilyId is null