Help needed with searching columns

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 2000 Forums

Transact-SQL (2000)

Help needed with searching columns

Author

Topic

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-02-15 : 19:40:27

Guys,
I am developing a stored procedure to search for column matches before inserting a row into the table. If a match if found, I want the procedure to return the id of the matching row, the matched column, and the matched value. I scripted a temp table to illustrate whats going on here:


CREATE TABLE #testMe (test_id INT IDENTITY, test_name VARCHAR(15), test_phone VARCHAR(7))
INSERT INTO #testMe

SELECT 'Nathan','1234567' UNION SELECT 'Mike','0987654' UNION SELECT 'John','1234567'


DECLARE @search_name 	VARCHAR(15),
	@search_phone 	VARCHAR(7)

SELECT 
	@search_name 	= 'Nathan',
	@search_phone 	= '1234567'
SELECT 
	test_id, 
	CASE
		WHEN test_name 	= @search_name THEN 'test_name'
		WHEN test_phone = @search_phone THEN 'test_phone'
	END AS 'matched_column',
	CASE
		WHEN test_name 	= @search_name THEN test_name
		WHEN test_phone = @search_phone THEN test_phone
	END AS 'matched_value'
FROM #testMe
WHERE test_name = @search_name OR test_phone = @search_phone

This result is not what want:


test_id     matched_column matched_value   
----------- -------------- --------------- 
1           test_phone     1234567
3           test_name      Nathan

I want to see all id matches (notice the id=3 phone is omitted):


test_id     matched_column matched_value   
----------- -------------- --------------- 
1           test_phone     1234567
3           test_phone     1234567      
3           test_name      Nathan

I think I have a problem with my case structure, because it "gets out" when it finds a match.

Any ideas for accomplishing this most efficiently? Any help is MUCH appreciated.

Thanks all,

Nathan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-02-16 : 03:53:07

Try this

CREATE TABLE #testMe (test_id INT IDENTITY, test_name VARCHAR(15), test_phone VARCHAR(7))
INSERT INTO #testMe

SELECT 'Nathan','1234567' UNION SELECT 'Mike','0987654' UNION SELECT 'John','1234567'

DECLARE @search_name VARCHAR(15),
@search_phone VARCHAR(7)
SELECT
@search_name = 'Nathan',
@search_phone = '1234567'

Select * from (
SELECT
test_id,
CASE
WHEN test_name= @search_name THEN 'test_name'

END AS 'matched_column',
CASE
WHEN test_name = @search_name THEN test_name

END AS 'matched_value'
FROM #testMe
WHERE test_name = @search_name or test_phone = @search_phone

union

SELECT
test_id,
CASE

WHEN test_phone = @search_phone THEN 'test_phone'

END AS 'matched_column',
CASE

WHEN test_phone = @search_phone THEN test_phone

END AS 'matched_value'
FROM #testMe
WHERE test_name = @search_name or test_phone = @search_phone
) t where matched_value is not null and matched_column is not null
order by test_id ,matched_value

drop table #testme

Madhivanan

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-02-16 : 15:17:26

I guess I was looking for a solution other than the UNION. I will be using several additional columns. Is there a way to do this in one select?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-16 : 15:32:33

Try this:


Declare @testMe table (test_id INT identity(1,1), test_name VARCHAR(15), test_phone VARCHAR(7))
Insert Into @testMe
Select 'Nathan','1234567' 
Union Select 'Mike','0987654' 
Union Select 'John','1234567'

Declare @searchMe table (search_id INT identity(1,1), search_name VARCHAR(15), search_phone VARCHAR(7))
Insert Into @searchMe
Select 'Nathan',null
Union Select null,'1234567' 

SELECT 
	test_id, 
	CASE
		WHEN test_name 	= search_name THEN 'test_name'
		WHEN test_phone = search_phone THEN 'test_phone'
	END AS 'matched_column',
	CASE
		WHEN test_name 	= search_name THEN test_name
		WHEN test_phone = search_phone THEN test_phone
	END AS 'matched_value'
FROM @testMe A
Inner Join @searchMe B
On A.test_name = B.search_name 
OR A.test_phone = B.search_phone

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources