SQLTeam.com | Weblogs | Forums

How to update status where no data returned from select statement?

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

What does the function do - and how is it written? Does that function always return a value for any/all input parameters?

If the function does not return values if the items don't exist - change the call to an OUTER APPLY and then you can check for NULL returned and use either ISNULL or COALESCE.