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/nvarchar
--得到i的值
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`
declare @fdname varchar(250) --字段名
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