Exec failed because name is not a valid identifier?

Question

I have a query, I have to run it as a dynamic query to output a meaningful column name. For example, if I run a query directly, it will correctly return the data. However, if I use the code below, 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 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 due to a complete connection?

Answer 1

Try this at the end:

 exec (@query) 

If you do not have parentheses, SQL Server takes the value of the variable as the name of the stored procedure.

OR

 EXECUTE sp_executesql @query 

And this should not be due to FULL JOINING.
But I hope that you have already created temporary tables: #TrafficFinal, # TrafficFinal2, # TrafficFinal3 before that.

---

Note that there are performance considerations with EXEC and sp_executesql. Because sp_executesql uses forced caching as sp.
More details here .

---

In another note, is there a reason why you use dynamic sql for this case, when you can use the query as is, given that you are not manipulating the queries and doing it the way it is?

Answer 2

As in my case, if your sql is generated by concatenation or uses converts, then sql at execution should have a prefix with the letter N, as shown below

eg.

 Exec N'Select bla..' 

N defines a string literal - this is unicode.