SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Search table on all columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nietzky
Yak Posting Veteran

72 Posts

Posted - 01/03/2013 :  16:34:04  Show Profile  Reply with Quote
SELECT RecID, ServerName, projectID, PM, Environment, STATUS, DEPLOY_STAGE1, DEPLOY_STAGE2, PRIORITY, LOCATION, LOCATION_ORIGINAL, PM_ROLE, DO_TYPE from MYTABLE

I would like to write an efficient query to search and find a string value in multiple columns of a table. From .net C# app I have a textbox with a search button. User puts in a string and all rows that match that string come back. The string can be in several columns. The sp should take only 1 paramater - the search string. These are all columns on which I would liek to conduct the search.
ServerName, projectID, PM, Environment, STATUS, PRIORITY, LOCATION, PM_ROLE,DO_TYPE

Thank you for any help

Edited by - nietzky on 01/03/2013 16:47:10

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/04/2013 :  02:42:34  Show Profile  Reply with Quote
you need to make use catalog view INFORMATION_SCHEMA.COLUMNS for this

see here for similar logic

http://www.mssqltips.com/sqlservertip/1522/searching-and-finding-a-string-value-in-all-columns-in-a-sql-server-table/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

nietzky
Yak Posting Veteran

72 Posts

Posted - 01/04/2013 :  06:17:13  Show Profile  Reply with Quote
How do I pass the variable to that sp if database is MyDB and table is MYTABLE?
Could I just use the logic below? I have no more than 10K records to search, records are archived every year so the size is not an issue.

SELECT RecID, ServerName, projectID, PM, Environment, STATUS, DEPLOY_STAGE1, DEPLOY_STAGE2, PRIORITY, LOCATION, LOCATION_ORIGINAL, PM_ROLE, DO_TYPE from MYTABLE

where
ServerName like @string
OR
projectID like @string
OR
PM like string
OR
Environment like @string
OR
and so on

Edited by - nietzky on 01/04/2013 06:22:06
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3377 Posts

Posted - 01/04/2013 :  06:28:37  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
That looks like the easiest way to do it as you have a fixed number of columns for the search.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/04/2013 :  10:31:43  Show Profile  Reply with Quote
if its a small dataset then that looks like a good method

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

nietzky
Yak Posting Veteran

72 Posts

Posted - 01/04/2013 :  19:16:36  Show Profile  Reply with Quote
Thank you
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/04/2013 :  20:41:20  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000