SQL Server Forums - Search across two fields returning zero records using "AND"

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

SQL Server Forums

Register Now and get your question answered!

Username:	Password:
Save Password
Forgot your Password?

All Forums

Old Forums

CLOSED - General SQL Server

Search across two fields returning zero records using "AND"

Forum Locked

Printer Friendly

Author

Topic

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/29/2004 : 08:25:59

Jason Hill writes "Hi

I am trying to search across two fields in a table using "LIKE", "AND" & wildcards.
The procedures below work using "OR" obviously returning records out of 1 field or another which is not my aim.
When i try "AND" in replacement of "OR" no records are returned.

I am aware that Microsoft say

"A string comparison using a pattern containing char and varchar data may not pass a LIKE comparison because of how the data is stored."

I have tried text as an alternative with the same zero record results. Am I confusing the table by having data stored as varchar then altering the field to text?

The procedures are being created within Enterprise Manager on "service manager 8"

I have been able to create an AND range comparison using money without wildcards and using
"WHERE prop_price > @prop_min AND prop_price < @prop_max"

So I really would appreciate if you could help solve my character problems!!

Kind Regards

Jay

---------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE authuser.p_searchNEWapproach
@prop_town text = '%',
@prop_class text = '%'

AS

SELECT property_id, prop_number, prop_address, prop_class, prop_town

FROM t_property

WHERE prop_class LIKE @prop_class AND prop_town LIKE @prop_class
GO

-------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE authuser.p_search4
@prop_address varchar (400),
@prop_class varchar (100)

AS

SELECT @prop_address = RTRIM(LTRIM (@prop_address))
SELECT @prop_class = RTRIM(LTRIM (@prop_class))
SELECT property_id, prop_number, prop_address, prop_class, prop_town

FROM t_property

WHERE prop_address LIKE '%' + @prop_address + '%' AND prop_class LIKE '%' + @prop_class + '%'
GO

---------------------------------------------------------------------------------------------------------------------"

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 10/29/2004 : 08:54:28

Your query looked like it should work, so do you have some sample data?

I tried the following out and it works.


Declare @myTable table (col1 varchar(100), col2 varchar(100))
Insert Into @myTable
Select 'Apple', 'Blue'
Union All Select 'Green Apple', 'Red'
Union All Select 'Orange', 'Blue-Green'

Declare @search1 varchar(100),
	@search2 varchar(100)

Set @search1 = 'Apple'
Set @search2 = ''

Select * From @myTable where col1 like ('%'+@search1+'%') and col2 like ('%'+@search2+'%')

Set @search1 = ''
Set @search2 = 'Blue'

Select * From @myTable where col1 like ('%'+@search1+'%') and col2 like ('%'+@search2+'%')

Set @search1 = 'Apple'
Set @search2 = 'Red'

Select * From @myTable where col1 like ('%'+@search1+'%') and col2 like ('%'+@search2+'%')

Corey

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 10/29/2004 : 17:17:47

In your "NEW" approach, you have your sproc parameters defined as text data type. I don't think you can actually pass Text data types to and from sprocs. Why not leave them as varchar?

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

Topic

Forum Locked

Printer Friendly

Jump To:

SQL Server Forums

This page was generated in 0.06 seconds.