Geek Answers Handbook

Separate union of choice. Errors in the ntext data type

I'm pretty useless in SQL, but I have to write a stored procedure for a very simple keyword phrase search.

I'm trying to make a simple choice in Name - using, for example,% keyword%, - then another select on Description -same keyword- and join (union), which selects 2.

However, I get an error message:

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

I tried to use UNION ALL , but in some cases returned duplicate lines (depending on the keyword / phrase).

I also tried to figure out how to use temporary tables and choose a different one from this, but that I was really confused.

Rules:

  • I can not change the data type
  • I need the lines from select to "Name" to be above the lines from the one selected in "Description"
  • I can use only 1 stored procedure and cannot change the data adapter, since I connect it to a system in which I do not control.

Additional Information:

Columns of tables (important 2 that I work 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 received from the table of various rows would really be appreciated. Also, explaining to me how Lyman would be great. :)

+8
sql sql-server sql-server-2008-r2
source share
3 answers

From the comments. This seems to be what you need.

 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 ( Products.Description like '%' + @Keyword + '%' or Products.Name like '%' + @Keyword + '%' ) ORDER BY CASE WHEN Products.Name like '%' + @Keyword + '%' THEN 0 ELSE 1 END

Perhaps you should consider using Full-Text Search for this, since a search using leading wildcards cannot use an index and you should always scan all the lines.

+4
source share

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

+7
source share

This answer for others like me will not have problems with duplicate lines.

UNION ALL , probably you want.

See the documentation for the UNION statement .

+5
source share

More articles:


All Articles