Greetings allI am attempting to search a full text indexes table, with optional search fields. However my query doesn't appear to be working.I'm searching an address database, which has the house number, street name and postcode queried. I want any of these fields to be searched, but they are all optional.So the postcode can be searched, without specifying a house number or street name.The following is my current SQLCREATE PROCEDURE sppb_AddressPoint_AddressCompletion@HouseNumber VARCHAR( 50 ),@StreetName VARCHAR( 150 ),@Postcode VARCHAR( 8 )/****************************************************************************************16 September 2004 - Created - Peter BridgerComplete an address, based on the data passed in***************************************************************************************/ASSET NOCOUNT ONSELECT TOP 50 -- Only retrieve top 50 recordsX,Y,ORGANISATION_NAME 'OrganisationName',BUILD_NUMBER 'HouseNumber',THOROUGHFARE_NAME 'StreetName',PostTown_Name 'PostTown', DDEP,Postcode 'Postcode'FROMAddressPointWHERE( -- Postcode LEN( @PostCode ) = 0 OR CONTAINS ( Postcode, @Postcode ))AND( -- Building number LEN( @HouseNumber ) = 0 OR CONTAINS ( BUILD_NUMBER, @HouseNumber ))AND( -- Street name LEN( @StreetName ) = 0 OR CONTAINS ( THOROUGHFARE_NAME, @StreetName ))
I thought my use of LEN( @StreetName ) = 0
would mean that if an empty parameter was passed in, a search wouldn't be performed. But when I do a search with optional parameters, it seems to just hang.What am I doing wrong? 
Full text indexing - Too many CONTAINs?
Posted - 2005-08-03 : 08:54:13
