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 2008 Forums
 Transact-SQL (2008)
 Search table on all columns

Author  Topic 

nietzky
Yak Posting Veteran

75 Posts

Posted - 2013-01-03 : 16:34:04
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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-04 : 02:42:34
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 - 2013-01-04 : 06:17:13
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
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2013-01-04 : 06:28:37
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

52326 Posts

Posted - 2013-01-04 : 10:31:43
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 - 2013-01-04 : 19:16:36
Thank you
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-04 : 20:41:20
welcome

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

Go to Top of Page
   

- Advertisement -