Coalesce FETCHING 0 ROWS inside stored procedure but fetches result as a query

ALTER PROCEDURE [dbo].[Status_yearly]
(@MID NVARCHAR(max))
AS
BEGIN

DECLARE @Years nvarchar(max)
SELECT @Years = Stuff((SELECT DISTINCT ',[' 
                                       + Cast(Year(u.StartDate) AS 
                                       NVARCHAR(4 
                                              )) 
                                       + ']' 
                       FROM   [products] u 
                       FOR xml path('')), 1, 1, '') 
DECLARE @date nvarchar(max)
SET @date =CONVERT(VARCHAR(10),GETDATE(),105)
-- SELECT @date
DECLARE @SQL nvarchar(max)
SELECT @SQL = '
select
    *
from (
    select
      ''Product'' AS Status,''total_product'' as [YEAR], 
      productname, year(u.StartDate) as [y]
    from products u  WHERE 
    MID ='''+@MID+''' 
    AND u.Flag <> 0 

  ) Data
PIVOT (
  COUNT(productname)
  FOR [y]
  IN (
    ' + @Years + '
  )
) PivotTable

UNION ALL 
select * from ( select ''Finished'' AS Finished, ''In Time'' as [YEAR], Coalesce(COUNT(productsname),0) AS productsname, 
Coalesce(COUNT(year(u.StartDate)),0) as [y] 
from products u
 WHERE u.AStartdate IS NOT NULL
 AND u.AStartdate = u.pStartdate 
AND MID ='''+@MID+''

 ) Data
PIVOT (
  COUNT(productsname)
  FOR [y]
  IN (
    ' + @Years + '
  )
) PivotTable

'
EXECUTE (@SQL)
END

This gives me the result

Status     Year            2012
Product   total_product     4
FINISHED  In Time           0






select ''Finished'' AS Finished,
      ''In Time'' as [YEAR], 
     Coalesce(COUNT(productsname),0) AS productname, Coalesce(COUNT(year(u.StartDate)),0) as [y]
    from products u  WHERE  u.AStartdate IS NOT NULL 
    AND u.AStartdate = u.pStartdate
    AND MID ='''+@MID+''' 

This is giving me result like

Finished   YEAR     productname  Y
FINISHED  In Time        1       1

Individually it gives me count as 1 but when executed inside stored procedure it give me 0.

SO how do I implement this inside the procedure? Please note some time there may not be any result set in the 'finished' block that is why I am using

Coalesce(COUNT(productsname),0) AS productname, Coalesce(COUNT(year(u.StartDate)),0) 

please suggest..