Distinct Union on Select. Errors on ntext data type

Question

I'm pretty useless at SQL but I find myself having to write a stored procedure for a very simple keyphrase search.

I am trying to do a simple select on Name -using like %keyword%- then another select on Description -same keyword- and joining (union) the 2 selects.

However, I get the error:

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

I tried using UNION ALL but that returned duplicate rows in certain instances (depending on the keyword/phrase).

I also tried to work out using temp tables and selecting distinct on that, but that's where I got really confused.

Rules:

• I can't change the data type
• I need the rows from select on the 'Name' to be above the rows from the select on the 'Description'
• I can only use 1 stored procedure, and can't change the data adapter, as I'm plugging it into a system I have no control over.

Further info:

Table Columns (the important 2 I am working with are Name and Description):

ProductId   int
Name    varchar(255)
Introduction    varchar(255)
Description ntext
Material    ntext
Colour  varchar(255)
Active  bit
Dimensions  varchar(255)
Photo   varchar(255)
Price   decimal(10, 2)
DisplayOrder    int
ProductReference    varchar(255)
CategoryId  int
FriendlyURL varchar(1000)

SQL:

(SELECT        Products.ProductId, Name, Introduction, Description, Active, 
            Material, Colour, Dimensions, Photo, Price, DisplayOrder, FriendlyURL,
            ProductReference, Categories_Products_Lookup.CategoryId
FROM            Products INNER JOIN
                Categories_Products_Lookup ON 
                Products.ProductId = Categories_Products_Lookup.ProductId
WHERE           Active = 1 AND tProduct.Name like '%'+@Keyword+'%')
UNION
(SELECT        Products.ProductId, Name, Introduction, Description, Active, 
            Material, Colour, Dimensions, Photo, Price, DisplayOrder, FriendlyURL,
            ProductReference, Categories_Products_Lookup.CategoryId
FROM            ProductsINNER JOIN
                Categories_Products_Lookup ON 
                Products.ProductId = Categories_Products_Lookup.ProductId
WHERE           Active = 1 AND Products.Description like '%'+@Keyword+'%')

Any help getting out a table of distinct rows would be really appreciated. Also, explaining to me as a Layman would be great. :)

Answer 1

Use something like 'cast(Description as nvarchar(2000)) as Description' instead of ntext field names.

Answer 2

This answer is for others like me that won't have problems with duplicate rows.

UNION ALL is probably what you want.

See the documentation for the UNION operator.