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 2005 Forums
 Transact-SQL (2005)
 iterate through many columns to search for a value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jayram
Starting Member

47 Posts

Posted - 04/24/2014 :  11:55:12  Show Profile  Reply with Quote
i have table like below

CREATE TABLE [dbo].[SAMPLE](
[COL_01] [nvarchar](50) NULL,
[COL_02] [nvarchar](50) NULL,
[COL_03] [nvarchar](50) NULL,
[COL_04] [nvarchar](50) NULL,
[COL_05] [nvarchar](50) NULL,
[COL_06] [nvarchar](50) NULL,
[COL_07] [nvarchar](50) NULL,
[COL_08] [nvarchar](50) NULL,
[COL_09] [nvarchar](50) NULL,
[COL_10] [nvarchar](50) NULL,
[COL_11] [nvarchar](50) NULL,
[COL_12] [nvarchar](50) NULL,
[COL_13] [nvarchar](50) NULL,
[COL_14] [nvarchar](50) NULL,
[COL_15] [nvarchar](50) NULL,
[COL_16] [nvarchar](50) NULL,
[COL_17] [nvarchar](50) NULL,
[COL_18] [nvarchar](50) NULL
) ON [PRIMARY]

i want to search for a value like 'Unknown' in each of these columns. how do iterate through these 18 columns to find a value 'Unknown'

Thanks

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 04/24/2014 :  13:14:50  Show Profile  Visit robvolk's Homepage  Reply with Quote
1. SELECT * FROM SAMPLE
WHERE [COL_01] = 'Unknown' OR
[COL_02] = 'Unknown' OR
[COL_03] = 'Unknown' OR
[COL_04] = 'Unknown' OR
[COL_05] = 'Unknown' OR
[COL_06] = 'Unknown' OR
[COL_07] = 'Unknown' OR
[COL_08] = 'Unknown' OR
[COL_09] = 'Unknown' OR
[COL_10] = 'Unknown' OR
[COL_11] = 'Unknown' OR
[COL_12] = 'Unknown' OR
[COL_13] = 'Unknown' OR
[COL_14] = 'Unknown' OR
[COL_15] = 'Unknown' OR
[COL_16] = 'Unknown' OR
[COL_17] = 'Unknown' OR
[COL_18] = 'Unknown'

2. Don't design your table that way. Having multiple columns of the same type of data is a poor design practice and should be avoided, as the above query demonstrates. Use something like this:
CREATE TABLE Sample(TermNumber int not null, SearchTeam nvarchar(50) not null)

INSERT Sample VALUES(1,'Unknown')
INSERT Sample VALUES(1,'Something')
INSERT Sample VALUES(2,'Something Else')

SELECT * FROM Sample WHERE SearchTerm='Unknown'
This query will never need to change and you can expand beyond the 18 search columns you currently have without modifying the table structure.
Go to Top of Page

jayram
Starting Member

47 Posts

Posted - 04/24/2014 :  13:34:30  Show Profile  Reply with Quote
Thanks but the data table comes from a client and i currently use something like this to generate a SQL

SELECT 'Select * from SAMPLE where '+ c.name + ' = ''Unknown'''
FROM sysobjects o
INNER JOIN syscolumns c ON c.id = o.id
INNER JOIN systypes t ON t.xusertype = c.xusertype
WHERE o.name = 'SAMPLE'
AND C.name like ( 'COL_%' )

But i was wondering if it could be wrapped in a cursor or stored procedure.

Edited by - jayram on 04/24/2014 13:39:25
Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 04/24/2014 :  18:00:05  Show Profile  Visit robvolk's Homepage  Reply with Quote
You could use a cursor but it will likely be slower. Are you saying the client's data has a dynamic number of columns?
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.06 seconds. Powered By: Snitz Forums 2000