TSQL - Case on Ntext (SQL 2005)

Question

Hi,

Stored Procedures in SQL 2005 - with field type NText

Im Writing a stored procedure to tidy up some data before importing it into Microsoft CRM. So far all works fine.

However i need to do a case statement on a nText Field. It needs to check this field against about 3 or 4 text values and set a new field (already in the destination table) which is also an nText field.

However i am getting the error "The data types ntext and varchar are incompatible in the equal to operator.

I have come across a few articles however their solutions all seem very complex.

Thanks for your help and advice in advanced.

Answer 1

NTEXT is deprecated in SQL Server 2005. You should use NVARCHAR(MAX) instead (NVARCHAR(MAX) can be used in CASE). Is it possible for you to change the type?

Answer 2

I recommend, if at all possible, replacing the NTEXT type with NVARCHAR(MAX), since NTEXT is not a first class type and NVARCHAR is. This should be easy to do with an ALTER TABLE statement.

Most higher level code shouldn't care about the type change. Any procedural code that uses READTEXT, WRITETEXT, etc. to deal with the NTEXT columns can be simplified to just basic selects and updates.

If the type change is not possible you may have to wrap the comparisons and assignments with CAST() or CONVERT() operators, which is ugly.

Answer 3

this works as well

CREATE TABLE #TEMP
(
    MyDummy NTEXT
)

INSERT INTO #TEMP (MyDummy) Values ('test')

SELECT 
CASE CAST(MyDummy AS NVARCHAR(MAX)) WHEN 'test' THEN 'ok' ELSE 'NOK' END MyTest
FROM #temp

drop table #temp