exec failed because the name not a valid identifier?

Question

I have a query I need run it as a dynamic query to output a meaningful column name. As an example, if I run the query directly, it returns data correctly. However, if I use below code, it shows:

The name '             SELECT (CASE WHEN A.Domain IS NOT NULL THEN A.Domain ELSE B.Domain END) AS [Domain],                      (CASE WHEN A.Email IS NOT NULL THEN A.Email ELSE B.Email END) AS [Email],                      A.[Sender Size] AS [Sender Size 1], A.[Sender Count] AS [Sender Count 1],                         A.[Receiver Size] AS [Receiver Size 1], A.[Receiver Count] AS [Receiver Count 1],                     A.[Sender Size 2] AS [Sender Size 2], A.[Sender Count 2] AS [Sender Count 2],                         A.[Receiver Size 2] AS [Receiver Size 2], A.[Receiver Count 2] AS [Receiver Count 2],                     B.SenderSize AS [Sender Size Average], B.SenderCount AS [Sender Count Average],                         B.ReceiverSize AS [Receiv' is not a valid identifier. 

Below it is the code:

DECLARE @query NVARCHAR(4000) SET @query = N'SELECT *             FROM             (                 SELECT (CASE WHEN A.Domain IS NOT NULL THEN A.Domain ELSE B.Domain END) AS [Domain],                          (CASE WHEN A.Email IS NOT NULL THEN A.Email ELSE B.Email END) AS [Email],                          A.[Sender Size] AS [Sender Size 1], A.[Sender Count] AS [Sender Count 1],                             A.[Receiver Size] AS [Receiver Size 1], A.[Receiver Count] AS [Receiver Count 1],                         A.[Sender Size 2] AS [Sender Size 2], A.[Sender Count 2] AS [Sender Count 2],                             A.[Receiver Size 2] AS [Receiver Size 2], A.[Receiver Count 2] AS [Receiver Count 2],                         B.SenderSize AS [Sender Size Average], B.SenderCount AS [Sender Count Average],                             B.ReceiverSize AS [Receiver Size Average], B.ReceiverCount AS [Receiver Count Average]                 FROM                     (                     SELECT (CASE WHEN tf.Domain IS NOT NULL THEN tf.Domain ELSE tf2.Domain END) AS Domain,                              (CASE WHEN tf.Email IS NOT NULL THEN tf.Email ELSE tf2.Email END) AS Email,                           ISNULL(tf.SenderSize,0) AS [Sender Size] , ISNULL(tf.SenderCount,0) AS [Sender Count], ISNULL(tf.ReceiverSize,0) AS [Receiver Size], ISNULL(tf.ReceiverCount,0) AS [Receiver Count],                           ISNULL(tf2.SenderSize,0) AS [Sender Size 2], ISNULL(tf2.SenderCount,0) AS [Sender Count 2], ISNULL(tf2.ReceiverSize,0) AS [Receiver Size 2], ISNULL(tf2.ReceiverCount,0) AS [Receiver Count 2]                      FROM #TrafficFinal tf FULL JOIN #TrafficFinal2 tf2 ON (tf.Email = tf2.Email AND tf.Domain = tf2.Domain)                     ) A FULL JOIN #TrafficFinal3 B ON (A.Email = B.Email AND A.Domain = B.Domain)             ) C             ORDER BY Domain, Email';      PRINT @query;  -- run it exec @query; 

Is it because of the full join?

Answer 1

Try this instead in the end:

exec (@query) 

If you do not have the brackets, SQL Server assumes the value of the variable to be a stored procedure name.

OR

EXECUTE sp_executesql @query 

And it should not be because of FULL JOIN.
But I hope you have already created the temp tables: #TrafficFinal, #TrafficFinal2, #TrafficFinal3 before this.

---

Please note that there are performance considerations between using EXEC and sp_executesql. Because sp_executesql uses forced statement caching like an sp.
More details here.

---

On another note, is there a reason why you are using dynamic sql for this case, when you can use the query as is, considering you are not doing any query manipulations and executing it the way it is?