Query by Column Name - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Query by Column Name

Author

Topic

Dorffius
Starting Member

36 Posts

Posted - 2002-03-01 : 11:48:33

I have one recordset that returns all of the column names from a table. I want to be able to base the next recordset on the value of the fields that reside under that column name for a certain record(CSS#).

The first recordset works fine using INFORMATION_SCHEMA.COLUMNS

"SELECT * FROM ETATEMP WHERE Recordset2.Fields.Item("COLUMN_NAME").Value = ????????? AND CCS = '" & target & "'"

Thanks in advance.

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-03-20 : 17:52:49

I'm not sure I've quite understood, but I think this might help:

It's an sp which searches ALL NVARCHAR FIELDS of a given table. Sorry I don't have time to generalise it to all field types...

create procedure spSearchAllFields
@sTableName nvarchar(50),
@sSearchPattern nvarchar(50)
as
set nocount on

declare @sSQL nvarchar(1000)

--get column names
create table #b (colname nvarchar(50),collist nvarchar(1000))
insert into #b
select syscolumns.name,NULL
from sysColumns inner join sysobjects
on syscolumns.id = sysobjects.id
where sysobjects.name = @sTableName
and syscolumns.xusertype = 231 --nvarchar only

--build csv column name list where clause
declare @sWhereClause nvarchar(1000)
set @sWhereClause = ''
update #b
set @sWhereClause = collist = @sWhereClause + 'or ' + colname + ' like ''' + @sSearchPattern + ''' '

--strip off leading 'or '
set @sWhereClause = substring(@sWhereClause, 4,len(@sWhereClause)-3)

--collate SQL
set @sSQL = 'select * from ' + @sTableName + ' where ' + @sWhereClause

exec (@sSQL)

drop table #b
go

as they say in the classics - HTH

(PS I stole the idea from graz - http://www.sqlteam.com/item.asp?ItemID=765 )

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources