How to handle empty rows in CROSS APPLY [SQL Server]

Question

I've below Stored Procedure-

ALTER PROCEDURE [dbo].[Optimized_GetArticlePostAMP]                                                              
(                                                                                    
 @PostID int                                                                                    
)                                                                                          

AS                                                                                    
BEGIN                                                                                    
SET NOCOUNT ON;                                                                                    
SET STATISTICS TIME ON                                                    

DECLARE @SectionId int ,@datediff int                                            
DECLARE @postdate datetime

 SELECT P.PostId, P.SectionID, P.PostName,MP.MetaTitle,P.Postdate,P.PostAuthor,P.IsApproved,                                                                                             
 MP.Metadescription, MP.Metakeywords,ISNULL(MP.IsRobotsMetaTag,0) as IsRobotsMetaTag,p.TotalViews, P.Subject, P.FormattedBody,                                                                                               
 MV.Isvideo,MV.VideoCode,MV.VideoCaption, A.DrComment,A.SpanishURL, PS.RedirectUrl, Isnull(PS.IsRedirect,0) as IsRedirect,                                     
 ISNULL(A.CommentedBy,38633) as CommentedBy ,MP.Canonical as Canonical,ISNULL(MP.RRpopUP ,0) as RRpopUP,ISNULL(A.PrevPost,0) as PreviousPostId,        
 ISNULL(A.NextPost,0) as NextPostId,PS.StatusId ,dbo.[mercola_GetCommentCountForPost](@PostId) as TotalReplies, isnull(PA.[FileName],'') as FileName,         
 PRD.StoryImage, PRD.StoryContent, ISNULL(NULLIF(prd.ALT, ''), P.Subject) AS ALT, ISNULL(PR.ReferenceData,'')as ReferenceData,       
 MH.LastModifiedDate,    
 CASE WHEN CHARINDEX('<p><strong>By', FormattedBody, -1)=1 THEN LTRIM(SUBSTRING(REPLACE(CAST(FormattedBody as varchar(max)),'<p><strong>By ',''),0,CHARINDEX('<',REPLACE(cast(FormattedBody as varchar(max)),'<p><strong>By ',''))))   
 ELSE 'Dr.Mercola' END as Name  
 FROM cs_posts P        
 LEFT JOIN Mercola_NewsletterDetails A on (P.Postid = A.postid)                                                                                       
 LEFT JOIN Mercola_PostStatus PS on (PS.postid=p.postid)                                                    
 LEFT JOIN Mercola_PostMetatags MP on(P.postid=MP.Postid)                                                                                             
 LEFT JOIN Mercola_postVideo MV  on(P.postid=MV.Postid)                                                      
 LEFT JOIN CS_PostAttachments PA on P.PostId=PA.PostId AND PA.contenttype LIKE 'audio/mpeg' AND PA.FILENAME LIKE '%.mp3' AND PA.isremote = 1                                                    
 LEFT JOIN Mercola_PostRelatedData PRD on P.PostId=PRD.PostId                                                  
 LEFT JOIN Mercola_PostReferences PR on P.PostId=PR.PostId        
 CROSS APPLY (select top 1 LastModifiedDate from Mercola_ArticleModifiedHistory where Mercola_ArticleModifiedHistory.Postid = P.postid order by LastModifiedDate desc)MH                                                  
 where P.Postid = @Postid

Now, when I execute the above SP with the below PostID -

--[Mercola_Optimized_GetArticlePostAMP] 732490 I get the below data which is expected. As, the query inside cross apply has data for the above postID.

But now, when I execute the same SP with below different PostID -

--[Mercola_Optimized_GetArticlePostAMP] 40702 I get below empty data [rows]. As, the query inside cross apply does not have the data for the above postID Infact, other joins have the data.

Expected Result for above case - Return the data and assigning default value for the CROSS APPLY. How can I do it?

@gofr1 Thanks, it worked. But now i want to assign default value when it does not have data.. — , Jun 23 '16 at 10:02

score 16 · Accepted Answer · answered Jun 23 '16 at 10:05

16

Use OUTER APPLY instead of CROSS APPLY

To overrun NULLs use ISNULL(MH.LastModifiedDate, @DefaultValue) as LastModifiedDate

answered Jun 23 '16 at 10:05

gofr1

15,741
11
42
52

TheGameiswar · Answer 2 · 2016-06-23T10:05:28.140

Change your query to Outer Apply which will preserve the rows from left hand side even if there are no matches

   outer APPLY (select top 1 LastModifiedDate
    from ArticleModifiedHistory 
    where ArticleModifiedHistory.Postid = P.postid order by LastModifiedDate desc

Cross apply is similar to Inner Join,so you will get only matching rows,Outer apply is like Left join which will preserve your left table even if there are no matching rows

update:

if you want assign default value for outer apply row in case of null,just use IsNull in select

something like below :

select *,isnull(b.id,'defaultvalue') from test1 t1 
outer apply(select id from test2 t2 where t1.id=t2.id) b

Thanks it worked like charm. But i want to assign default value as it return `NULL` — , Jun 23 '16 at 10:01

How to handle empty rows in CROSS APPLY [SQL Server]

2 Answers2