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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nietzky
Yak Posting Veteran

75 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
52326 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

75 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
3383 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
52326 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

75 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
52326 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  
 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