dynamic sql query not showing result on server

Question

This query shows records when run on my local system, but when I deploy it to and run it on the server, the query does not show any records. When I connect to the DB server through sql engine and execute (alt + x), it only displays messages command completed successfully, but displays no records.

Any idea how to fix this, where is the issue?

DECLARE @cols AS NVARCHAR(MAX)
, @query  AS NVARCHAR(MAX) 
select @cols = STUFF
(
    (
        SELECT ',' + QUOTENAME(StartDateTime) 
        from
        (
            select distinct StartDateTime 
            from tblEmployeeShift 
            inner join tblShift on tblShift.ShiftId = tblEmployeeShift.ShiftId 
            where EmployeeNewId = 3126 
            and   IsDeleted = 0 
            and   StartDateTime >= '06/07/2014 12:00:00' 
            and StartDateTime <= '07/07/2014 12:00:00' 
        ) d 
        order by StartDateTime 
        FOR XML PATH(''), TYPE 
    ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,''
)

set @query = 
N'SELECT EmployeeNewId
,Full_Name
,' + @cols + N' 
from  
(
    select * 
    from 
    (
        select tblEmployeeShift.EmployeeNewId
        ,Full_Name
        ,StartDateTime
        ,dbo.GetAttendanceFlag(tblEmployeeShift.EmployeeNewId,StartDateTime) as PV 
        from tblEmployeeShift 
        inner join tblShift on tblShift.ShiftId = tblEmployeeShift.ShiftId 
        inner join employee on tblEmployeeShift.EmployeeNewId = SUBSTRING(employee.Employeeid,6,5) 
        where tblEmployeeShift.EmployeeNewId in 
        (
            select top 20 employeenewid 
            from employee e 
            where  e.EmployeeNewId = 3126 
        ) 
        and  IsDeleted = 0 
    ) d
) 
x pivot  
( 
    max(PV) for StartDateTime in (' + @cols + N') 
) c' 

execute sp_executesql @query 

Answer 1

Most likely the data on the server is such that the @cols variable is NULL, and the concatenation of the NULL @cols variable results in a NULL @query variable.