Cast dynamically created column in SQL statement


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?




cast @TableName2 to nvarchar then add .. !!

please try this

,'+ cast ( @TableName2 as nvarchar )
+'.[CommentsBox' + Cast(@ActNo2 AS NVARCHAR(5)) +'] '
+' AS Comments

@harishgg1 it's going back to the original issue:

The ntext data type cannot be selected as DISTINCT because it is not comparable.

So which of the variables is ntext?

It's the comments column that is ntext.

please see this link !!!

hope this helps !!

I don't see any column named comments in the SQL Script you posted

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;
1 Like

notice the bold section

  1. You have a concatenation happening there with + at end of a CAST( + AS NVARCHAR)
  2. as NVARCHAR with no length for

The CommentsBox field is numbered 1-10 in table and dynamic code iterates through these.