Stored proc not quite working as planned!!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Stored proc not quite working as planned!!!

Author

Topic

Dazza1883
Starting Member

5 Posts

Posted - 2009-01-22 : 07:36:31

I haven't really used stored procedures that much before but i am trying to use one which will freetext search 3 different colums in my table PAO(house number), STREET_DESCRIPTOR(street name) and POSTCODE(postcode). This is what i have so far:
ALTER PROCEDURE [dbo].[procStreetSearchNew]
-- Add the parameters for the stored procedure here
(
@STREET_DESCRIPTOR nvarchar(255)
)
AS

SELECT * FROM dbo.nlpg_master WHERE FREETEXT (STREET_DESCRIPTOR, @STREET_DESCRIPTOR) AND FREETEXT (PAO, @STREET_DESCRIPTOR) OR FREETEXT (POSTCODE, @STREET_DESCRIPTOR)
So when i search for 15 Harwood Road i get a return of all 15 harwood road sometimes if i enter 15 harwood this works sometimes when i try it with different combinations 15 brook i get nothing can anyone advise me please on where i am going wrong
Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-22 : 07:42:43

I think, at a first look, you have to check your conditions in where-clause regarding the use of AND and OR.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-22 : 07:46:21

R u looking for this

ALTER PROCEDURE [dbo].[procStreetSearchNew]
-- Add the parameters for the stored procedure here
(
@STREET_DESCRIPTOR nvarchar(255)
)
AS

SELECT * FROM dbo.nlpg_master WHERE ( STREET_DESCRIPTOR LIKE '%'+@STREET_DESCRIPTOR+'%' AND PAO LIKE '%'+@STREET_DESCRIPTOR+'%') OR POSTCODE LIKE '%'+@STREET_DESCRIPTOR+'%'

Dazza1883
Starting Member

5 Posts

Posted - 2009-01-22 : 08:25:06

Thanks for the response guys, I have tried your method raky with not much joy my problem is i am searching through a database of addresses for an entire city which has more that 1.5million records so as you can probably imagine this is quite some task it's for emergency response so a user can quickly enter the details that they Know into a single text field then query three of the colums quickly to get a response

Dazza1883
Starting Member

5 Posts

Posted - 2009-01-22 : 09:38:10

Is it possible to write this:
SELECT * FROM dbo.nlpg_master WHERE FREETEXT (STREET_DESCRIPTOR, @STREET_DESCRIPTOR) AND FREETEXT (PAO, @STREET_DESCRIPTOR) OR FREETEXT (POSTCODE, @STREET_DESCRIPTOR)
in any way like this:
SELECT * FROM dbo.nlpg_master WHERE FREETEXT (STREET_DESCRIPTOR, PAO, POSTCODE, @STREET_DESCRIPTOR)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources