Click here to Skip to main content
14,668,844 members
Rate this:
Please Sign up or sign in to vote.
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  
Posted
Updated 23-Aug-20 14:39pm
v2
Comments
Gerry Schmitz 24-Aug-20 9:57am
   
The problem is with "PartCheck"; it should return "something"; even if it's only "No Data".

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100