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
Advertisement
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 ;