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 2005 Forums
 Transact-SQL (2005)
 iterate through many columns to search for a value

Author  Topic 

jayram
Starting Member

47 Posts

Posted - 2014-04-24 : 11:55:12
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

15732 Posts

Posted - 2014-04-24 : 13:14:50
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 - 2014-04-24 : 13:34:30
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.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2014-04-24 : 18:00:05
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
   

- Advertisement -