이름이 유효한 식별자가 아니기 때문에 exec가 실패 했습니까?

이름이 유효한 식별자가 아니기 때문에 exec가 실패 했습니까?

---

의미있는 열 이름을 출력하기 위해 동적 쿼리로 실행해야하는 쿼리가 있습니다. 예를 들어 쿼리를 직접 실행하면 데이터가 올바르게 반환됩니다. 그러나 아래 코드를 사용하면 다음과 같이 표시됩니다.

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.

아래는 코드입니다.

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;

완전 조인 때문인가요?

---

대신 이것을 시도하십시오.

exec (@query)

대괄호가 없으면 SQL Server는 변수 값을 저장 프로 시저 이름으로 가정합니다.

또는

EXECUTE sp_executesql @query

FULL JOIN 때문이 아니어야합니다.
하지만이 전에 임시 테이블 #TrafficFinal, # TrafficFinal2, # TrafficFinal3을 이미 만드셨기를 바랍니다.

---

EXEC 사용과 sp_executesql 사용간에 성능 고려 사항이 있습니다. sp_executesql은 sp와 같은 강제 명령문 캐싱을 사용하기 때문입니다.
자세한 내용은 여기를 참조 하세요 .

---

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?

---

As was in my case if your sql is generated by concatenating or uses converts then sql at execute need to be prefixed with letter N as below

e.g.

Exec N'Select bla..' 

the N defines string literal is unicode.

참고URL : https://stackoverflow.com/questions/11991987/exec-failed-because-the-name-not-a-valid-identifier