How to update status where nothing retturn from select cross apply function?

Question

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 nothing returned from select statement then update status to nothing data returned

create table #TempPC
(
PartNumber NVARCHAR(300),
CompanyId INT,
Status nvarchar(200)
)
insert into #TempPC (PartNumber,CompanyId)
values
('9C06031A2R43FKHFT',1233345),
('VJ0805AIR5CXAMT',8433324)

when select below not return data then update status with nothing data returned

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 temppc not return data from select statement cross apply then update status with no data returned for this part

Expected Result

PartNumber CompanyId status
9C06031A2R43FKHFT 1233345 No 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

Answer

Hi @ahmed salah ,

Please refer below query and check whether it is helpful to you. Otherwise please provide more details about the function FN_PartCheck_Test so that we could copy and paste into a query window to develop a tested query.

update t  
set status='No data returned'  
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

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa

Answer

Hi @ahmed salah ,

Please provide the complete code of function '[PC].FN_PartCheck_Test' so that we could have a better understanding and test.

You could have another try with below:

update t  
set t.status='No data returned'  
from #TempPC t  
where not exists( select 1 from [PC].FN_PartCheck_Test( t.[PartNumber],0,1,1,t.CompanyId) pc  
Where pc.GroupID>-2 And pc.PortionID>-2  and pc.[PartNumber]=t.PartNumber)

If the response is helpful, please click "Accept Answer" and upvote it.

Best regards
Melissa

Share via

How to update status where nothing retturn from select cross apply function?

2 answers

Your answer