I have to do a select distinct on a particular dynamic SQL stamement and the column is an ntext, so I have to cast it as an NVARCHAR, I can't change the source column due to the table being created by our management system in the background, you create a comment column and it assigns it as a ntext.
The part of the statement already has some dynamic SQL in it and I'm trying to wrap the whole thing in a CAST as NVARCHAR but it keeps coming up with incorrect syntax near AS
The part of the dynamic statement is as below, the ActNo2 variable is part of a loop to build data into a temp table. This was working fine before I decided I need to use distinct.
,'+ @TableName2 +'.[CommentsBox'+ Cast(@ActNo2 AS NVARCHAR(5)) +'] AS Comments
so if I change it to:
,'+ CAST(@TableName2 +'.[CommentsBox'+ Cast(@ActNo2 AS NVARCHAR(5)) +'] '+ AS NVARCHAR) +' AS Comments
Incorrect syntax near the keyword 'AS'.
Can anyone see where I am going wrong?