the dumb way is to doselect columnname=case
when col1='yourcontent' then 'col1'
when col2='yourcontent' then 'col2'
...
else '' end from yourtablebut tryuse pubs
godeclare @content varchar(100)
declare @sql varchar(8000), @table varchar(30)
set @content = 'White'
select @table = 'authors', @sql = 'select top 1 columnname=case 'select @sql = @sql + ' when ' + name + ' = ''' + @content + ''' then ''' + name + ''' '
from syscolumns where object_name(id) = @table
and (xtype=167 or xtype=175 or xtype=239 or xtype=231) --only on char/nchar/vchar/nvarcharset @sql = @sql + ' else '''' end from ' + @tableexec(@sql)
when col1='yourcontent' then 'col1'
when col2='yourcontent' then 'col2'
...
else '' end from yourtablebut tryuse pubs
godeclare @content varchar(100)
declare @sql varchar(8000), @table varchar(30)
set @content = 'White'
select @table = 'authors', @sql = 'select top 1 columnname=case 'select @sql = @sql + ' when ' + name + ' = ''' + @content + ''' then ''' + name + ''' '
from syscolumns where object_name(id) = @table
and (xtype=167 or xtype=175 or xtype=239 or xtype=231) --only on char/nchar/vchar/nvarcharset @sql = @sql + ' else '''' end from ' + @tableexec(@sql)
declare @fdvalue varchar(500) --字段值
declare @chk bit --检测是否存在
declare @sql nvarchar(4000) --检测语句
set @fdvalue='要查询字段的值'
declare #tb cursor for select name form syscolumns where object_id('表名')=id
open #tb
fetch next from #tb into @fdname
while @@fetch_status=0
begin
set @sql='select @chk=case ['@fdname+'] when '''+@fdvalue+''' then 1 else 0 end from 表'
exec sp_executesql @sql,N'@chk bit output',@chk output
if @chk=1
begin
print @fdname
break
end
fetch next from #tb into @fdname
end
deallocate #tb
set @sql='select @chk=case ['@fdname+'] when '''+@fdvalue+''' then 1 else 0 end from 表'为:
set @sql='select @chk=case cast(['@fdname+'] as varchar) when '''+@fdvalue+''' then 1 else 0 end from 表'
表里面有一些字段:DATE1,DATE2,DATE3,......,DATEi
i不固定,随着系统的设置而变
这些列对应的值可能是20030101,20030102,20030103,......
就是按照日历顺次排下去,我现在想得到i的值thanks
create proc p_getfd
@值 int, --要查询的字段的值
@i int output --查询到的i的值
as
declare @fdname varchar(250) --字段名
declare @chk bit --检测是否存在
declare @sql nvarchar(4000) --检测语句
declare #tb cursor for select name form syscolumns where object_id('表')=id
open #tb
fetch next from #tb into @fdname
while @@fetch_status=0
begin
set @sql='select @chk=case ['@fdname+'] when '+cast(@fdvalue as varchar)+' then 1 else 0 end from 表'
exec sp_executesql @sql,N'@chk bit output',@chk output
if @chk=1
begin
set @i=cast(right(@fdname,len(@fdname)-4) as int)
break
end
fetch next from #tb into @fdname
end
deallocate #tb
go
--调用:
declare @i int
exec p_getfd 20030101,@i output
print @i`