Search several fields - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Search several fields

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-01-20 : 10:10:24

In my last query on this subject Peso gave me the correct answer and I wonder if he can help me again. Here is his sp which works fine. How can I pass another paramater of Surname and search the table for any matches of this parameter in RN_Surname plus any matches as alreay carried out by the sp.
That is I want to search by surname and address.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[SearchAllAddressNew]
(
	@Address AS NVARCHAR(100)
)
AS

DECLARE	@Parts TABLE
	(
		data VARCHAR(20)
	)

INSERT	@Parts
	(
		data
	)
SELECT	data
FROM	dbo.fnParseList(' ', @Address)

DECLARE	@n INT

SELECT	@n = COUNT(*)
FROM	@Parts

DECLARE	@Hit TABLE
	(
		ID INT
	)

INSERT		@Hit
		(
			ID
		)
SELECT		r.RN_ID
FROM		@Parts AS p
CROSS JOIN	tblRN_Register AS r
WHERE		r.RN_Add1 LIKE '%' + p.data + '%'
		OR r.RN_Add2 LIKE '%' + p.data + '%'
		OR r.RN_Add3 LIKE '%' + p.data + '%'
		OR r.RN_Add4 LIKE '%' + p.data + '%'
		OR r.RN_Pcode LIKE '%' + p.data + '%'

		GROUP BY	r.RN_ID
HAVING		COUNT(*) >= @n

SELECT		r.*
FROM		tblRN_Register AS r
INNER JOIN	@Hit AS h ON h.ID = r.RN_ID

TIA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-20 : 11:24:03

try like below


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[SearchAllAddressNew]
(
	@Address AS NVARCHAR(100),
        @Surname AS NVARCHAR(8000)=NULL
)
AS

DECLARE	@Parts TABLE
	(
		data VARCHAR(20)
	)

INSERT	@Parts
	(
		data
	)
SELECT	data
FROM	dbo.fnParseList(' ', @Address)

DECLARE	@n INT

SELECT	@n = COUNT(*)
FROM	@Parts

DECLARE	@Hit TABLE
	(
		ID INT
	)

INSERT		@Hit
		(
			ID
		)
SELECT		r.RN_ID
FROM		@Parts AS p
CROSS JOIN	tblRN_Register AS r
WHERE		(r.RN_Add1 LIKE '%' + p.data + '%'
		OR r.RN_Add2 LIKE '%' + p.data + '%'
		OR r.RN_Add3 LIKE '%' + p.data + '%'
		OR r.RN_Add4 LIKE '%' + p.data + '%'
		OR r.RN_Pcode LIKE '%' + p.data + '%')
AND (EXISTS(SELECT 1 FROM dbo.fnParseList(' ', @Surname) WHERE data=r.RN_Surname)
OR @Surname IS NULL)
		GROUP BY	r.RN_ID
HAVING		COUNT(*) >= @n

SELECT		r.*
FROM		tblRN_Register AS r
INNER JOIN	@Hit AS h ON h.ID = r.RN_ID

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-01-21 : 04:57:45

Thanks you visakh16. I can search now on name only, or name and address, but not if I only enter part of the address ......can you help me with this please.

Could you explain what 1 is also in (EXISTS(SELECT 1 FROM dbo.fnParseList so I can hopefully understand this sp

guruchi_20
Starting Member

24 Posts

Posted - 2009-01-21 : 05:06:44

it means if 1 have data is already exist you will not saved it.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-01-21 : 05:44:21

You mean if there is at least one match against @Surname ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 08:38:29

quote:
Originally posted by Pinto

You mean if there is at least one match against @Surname ?

that means records RN_Surname value matches any one among the values in csv list

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-01-21 : 08:47:54

Thanks you visakh16 for the explanation. Can you help me develop the search as I mentioned above so I can search on name only, address only or match both name and address ?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 08:52:14

quote:
Originally posted by Pinto

Thanks you visakh16 for the explanation. Can you help me develop the search as I mentioned above so I can search on name only, address only or match both name and address ?

Thanks

can you show how will you be executing sp when you pass address alone? (what will you be passing as value of name param?)

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-01-21 : 09:11:34

I am using a tableadapter on a windows form

Dim strAdd As String = Me.ToolStripTextBoxAddress.Text
Dim strSurname As String = Me.ToolStripTextBoxSurname.Text
Me.TblRN_RegisterTableAdapter.SearchAllAddsNewv2(Me.DsRNRegister.tblRN_Register, strAdd, strSurname)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 09:15:39

nope. i was asking what will be value passed for name parameter when you dont want to search on name?

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-01-21 : 10:03:24

Sorry, misunderstood. It's an empty textbox so when i step through code code value shows as ""

Does that help ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 11:07:58

then use this


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[SearchAllAddressNew]
(
	@Address AS NVARCHAR(100),
        @Surname AS NVARCHAR(8000)=NULL
)
AS

DECLARE	@Parts TABLE
	(
		data VARCHAR(20)
	)

INSERT	@Parts
	(
		data
	)
SELECT	data
FROM	dbo.fnParseList(' ', @Address)

DECLARE	@n INT

SELECT	@n = COUNT(*)
FROM	@Parts

DECLARE	@Hit TABLE
	(
		ID INT
	)

INSERT		@Hit
		(
			ID
		)
SELECT		r.RN_ID
FROM		@Parts AS p
CROSS JOIN	tblRN_Register AS r
WHERE		(r.RN_Add1 LIKE '%' + p.data + '%'
		OR r.RN_Add2 LIKE '%' + p.data + '%'
		OR r.RN_Add3 LIKE '%' + p.data + '%'
		OR r.RN_Add4 LIKE '%' + p.data + '%'
		OR r.RN_Pcode LIKE '%' + p.data + '%')
AND (EXISTS(SELECT 1 FROM dbo.fnParseList(' ', @Surname) WHERE data=r.RN_Surname)
OR NULLIF(@Surname,'') IS NULL)
		GROUP BY	r.RN_ID
HAVING		COUNT(*) >= @n

SELECT		r.*
FROM		tblRN_Register AS r
INNER JOIN	@Hit AS h ON h.ID = r.RN_ID

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-01-22 : 04:28:26

Thank you for all your help - that works perfectly. Just had to change @Surname AS NVARCHAR(8000)=NULL to

@Surname AS NVARCHAR(4000)=NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-22 : 09:00:22

welcome

Pinto
Aged Yak Warrior

590 Posts

Posted - 2009-01-23 : 09:39:56

I have found that if the address is not entered and the address fields all contain null, a search on the surname alone doesn't work. Is there a away around this ? If I delete the nulls from the table the search works so is there a way I can make the default value of the address fields a zero length string ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 10:34:48

then make default value '' and change condition accordingly

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources