I am trying to send the table data which comprises of 2 columns Status and Count through email. And I need to achieve this from stored procedure with DB send mail. I wrote the below code, but the variable @msgsource which is for body of the mail is returning NULL value. Can someone look into this?

Declare @msgsource varchar(max)

If object_id( Tempdb..#Temp1 ) is not null drop table #Temp1

-- To get the Submittable_Ready records before job run
Select  Submittable-Ready  as Status, Count(*) as [Count] Into #Temp1
from smt_837_Submitted_Housecalls_Claims clm(nolock)  
where clm.Edi_config_ref = 1 and 
clm.[Status] like  Submitted 
and cast (refresh_date as date) = cast(getdate() as date)

If object_id( Tempdb..#Temp2 ) is not null drop table #Temp2

-- To get the Submittable_Ready records after job run
Select  Submittable-Ready  as Status, Count(*) as [Count] Into #Temp2
from smt_837_Submitted_Housecalls_Claims clm(nolock)  
where clm.Edi_config_ref = 1 and 
clm.[Status] like  Submittable-Ready 
and cast (refresh_date as date) = cast(getdate() as date)



SET @msgsource =@msgsource+ <br> Submittable Ready records before job run,<br>  
<table border ="1"> <tr><p style="color:#FFFFFF">  
 <td bgColor="#808080" >Status</td>   
 <td bgColor="#808080" >Count</td>     </tr>  <p style="color:#00000">   
select @msgsource
SET @msgsource=@msgsource  
+Cast((SELECT [Status] as td,   ,cast([Count] as varchar(10)) as td,    
-- ,(Case when ([Comments] like  %Duplicate%  or [Comments] like  %Test% ) then Cast( <font 
color="Red"> +[Comments]+ </font>  as XML) else [Comments] end) as td,    
from #temp1  FOR XML PATH( tr ), ELEMENTS) as varchar(max))  
 + </table>   

 --select @msgsource

 SET @msgsource =@msgsource+ <br> Submittable Ready records after job run,<br>  
 <table border ="1"> <tr><p style="color:#FFFFFF">  
 <td bgColor="#808080" >Status</td>   
 <td bgColor="#808080" >Count</td>     </tr>  <p style="color:#00000">   
 
 SET @msgsource=@msgsource  
 +Cast((SELECT [Status] as td,   ,cast([Count] as varchar(10)) as td,    
 -- ,(Case when ([Comments] like  %Duplicate%  or [Comments] like  %Test% ) then Cast( <font 
 color="Red"> +[Comments]+ </font>  as XML) else [Comments] end) as td,    
 from #temp2  FOR XML PATH( tr ), ELEMENTS) as varchar(max))  
  + </table>   
 
  Select @msgsource