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
Instead of using '+' to concatenate - I would recommend using the CONCAT function. It can simplify your statements and takes care of NULL values for you.
I would also recommend posting the full query instead of just a portion - the problem could easily be somewhere else in the dynamic code and it is being masked.
Looking at this snippet - I would be very concerned about SQL injection. This format of concatenating into a SQL statement could easily be subject to that type of issue and there are many better ways to approach creating dynamic SQL that avoids these types of issues.
And finally - to figure out why there is an issue, you should print out the final dynamic SQL to see what the final query to be executed actually looks like.
With that said:
Declare @TableName2 sysname = 'MyTable2'
, @ActNo2 int = 12345
, @sqlCommand nvarchar(max) = '';
Set @sqlCommand = '
Select Column1
, Column2
, cast(' + concat(quotename(@TableName2), '.', quotename(concat('CommentsBox', cast(@ActNo2 As nvarchar(5))))) + ' As nvarchar(max)) As Comments';
Print @sqlCommand;