有一张表 tb1 col1 col2 col3
1 1.29 0.69 2.27
2 2.1 0.69 2.29
3 2.29 0.69 2.27想实现这样一个功能,自动的一次从第一列,第二列,第三列中寻找等于2.29的数字,并输出这条记录这是个试验,为了一张列数更多的表,请问我这样写哪些地方错了 declare @id int
declare @a varchar(200)set @id = 1
while @id < 3
set @a ='select name from syscolumns where id = object_id(''tb1'') and colorder =@id'
set @id = @id+1exec('select * from tb1 where' +@a+ '= 2.29')
end
1 1.29 0.69 2.27
2 2.1 0.69 2.29
3 2.29 0.69 2.27想实现这样一个功能,自动的一次从第一列,第二列,第三列中寻找等于2.29的数字,并输出这条记录这是个试验,为了一张列数更多的表,请问我这样写哪些地方错了 declare @id int
declare @a varchar(200)set @id = 1
while @id < 3
set @a ='select name from syscolumns where id = object_id(''tb1'') and colorder =@id'
set @id = @id+1exec('select * from tb1 where' +@a+ '= 2.29')
end
declare @id int
declare @a varchar(200)set @id = 1
while @id < 3
begin
set @a ='select name from syscolumns where id = object_id(''tb1'') and colorder =@id'
set @id = @id+1exec('select * from tb1 where' +@a+ '= 2.29')
end
declare @a varchar(200)set @id = 1
while @id < 3
select @a=name from syscolumns where id = object_id(''tb1'') and colorder =@id
set @id = @id+1exec('select * from tb1 where' +@a+ '= 2.29')
end
declare @a varchar(200)set @id = 1
while @id < 3
set @a ='select name from syscolumns where id = object_id(''tb1'') and colorder ='+@id
set @id = @id+1exec('select * from tb1 where' +@a+ '= 2.29')
end
declare @id int
declare @a varchar(200)set @id = 1
while @id < 3
select @a=name from syscolumns where id = object_id('tb1') and colorder =@id
exec('select * from tb1 where' +@a+ '= 2.29')
set @id = @id+1
declare @a varchar(200)set @id = 1
while @id < 3
set @a ='select name from syscolumns where id = object_id(''tb1'') and colorder ='+ltrim(@id)
set @id = @id+1exec('select * from tb1 where' +@a+ '= 2.29')
end
--打印你的sql出来,你自己看哪有问题
declare @id int
declare @a varchar(200)set @id = 1
while @id < 3
begin
set @a ='select name from syscolumns where id = object_id(''tb1'') and colorder =@id'
set @id = @id+1
endprint 'select * from tb1 where' +@a+ '= 2.29'/*
select * from tb1 whereselect name from syscolumns where id = object_id('tb1') and colorder =@id= 2.29
declare @sql varchar(max)
set @sql = 'select * from tb1 where 1 = 1 '
select @sql = @sql + ' or ' + [name] + ' = 2.29 '
from syscolumns
where id = object_id('tb1')
exec(@sql)
这样报错
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: 'name' 附近有语法错误。
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: 'name' 附近有语法错误。
那怎么才能让@a得到我要的那个name值呢?
这样了
服务器: 消息 170,级别 15,状态 1,行 1
第 1 行: 'max' 附近有语法错误。
服务器: 消息 137,级别 15,状态 1,行 3
必须声明变量 '@sql'。
服务器: 消息 137,级别 15,状态 1,行 3
必须声明变量 '@sql'。
服务器: 消息 137,级别 15,状态 1,行 6
必须声明变量 '@sql'。
declare @sql varchar(4000)
set @sql = ''
select @sql = @sql + ' or ' + [name] + ' = 2.29 '
from syscolumns
where id = object_id('tb1')
set @sql = 'select * from tb1 where ' + right(@sql,len(@sql) - 4)
exec(@sql)
declare @a varchar(200)set @id = 1
while @id < 3
select @a=name from syscolumns where id = object_id('tb1') and colorder =@id
set @id = @id+1
Print 'select * from tb1 where' + @a + '= 2.29'
exec('select * from tb1 where' + @a + '= 2.29')
end
--前面有正确答案了啊!
insert tb1
select 1, 1.29, 0.69, 2.27 union all
select 2, 2.1, 0.69, 2.29 union all
select 3, 2.29, 0.69, 2.27 declare @sql varchar(8000)
declare @sql1 varchar(8000)
set @sql1 = 'select * from tb1 where 1 = 1 and '
select @sql = isnull(@sql+' or ','') + [name] + ' = 2.29 '
from syscolumns
where id = object_id('tb1') and name<>'id'
set @sql=@sql1+'('+@sql+')'exec(@sql)id col1 col2 col3
----------- -------------------- -------------------- --------------------
2 2.10 .69 2.29
3 2.29 .69 2.27
create table tb1(id int,col1 decimal(10,2),col2 decimal(10,2),col3 decimal(10,2))
insert into tb1
select 1 ,1.29 ,0.69 ,2.27 union all
select 2 ,2.1 ,0.69 ,2.29 union all
select 3 ,2.29 ,0.69 ,2.27
godeclare @sql varchar(4000)
set @sql = ''
select @sql = @sql + ' or ' + [name] + ' = 2.29 '
from syscolumns
where id = object_id('tb1') and [name] not like 'id'
set @sql = 'select * from tb1 where ' + right(@sql,len(@sql) - 3)
exec(@sql)drop table tb1
/**************id col1 col2 col3
----------- --------------------------------------- --------------------------------------- ---------------------------------------
2 2.10 0.69 2.29
3 2.29 0.69 2.27(2 行受影响)