declare @a varchar(100),@n int set @n=5 ---自定 select @a='update a set '+col_name(object_id('a'),@n)+'=1' exec (@a)
declare @a varchar(100),@n int set @n=5 ---自定 select @a='update a set '+col_name(object_id('a'),@n)+'=1'---a是表名 exec (@a)
create table test(col1 int,col2 char(10),col3 int) insert test select 1,'a',3 union all select 4,'b',6select * from test declare @col varchar(20)--指定第二列 select @col=name from syscolumns where id=object_id('test') and colid=2 exec('update test set '+@col+'=''c''')select * from testdrop table test
if object_id('tbTest') is not null drop table tbTest GO create table tbTest(id int,col1 varchar(10),col2 varchar(10),col3 varchar(10)) insert tbTest select 1,'a','b','c' union all select 2,'a','b','c' union all select 3,'a','b','c' GOdeclare @ColName sysname,@sql varchar(2000) declare @n int set @n = 3 /*更新第三列*/ SELECT @ColName = name FROM syscolumns AS t WHERE id=object_id('tbtest') and (select count(*) from syscolumns where id = t.id and colorder <= t.colorder) = @n ----构建用于更新的SQL字符串 set @sql = 'update tbTest set ' + @ColName + ' = ''1'' where 1=1' ----执行更新 EXEC(@sql)----查看 select * from tbTest ----清除测试环境 drop table tbTest/*结果 id col1 col2 col3 1 a 1 c 2 a 1 c 3 a 1 c */
col_name(object_id('a'),@n)+'=1'中的'=1'应该是要改为的值了,如果改为其它字符串为什么错呢? :set @n=3 select @a='update a set '+col_name(object_id('a'),@n)+'=hi'---a是表名 exec (@a)
declare @a varchar(100),@n int set @n=1---自定 select @a='update a set '+col_name(object_id('a'),@n)+'=''a'''---a是表名 exec (@a)原来只多加一个单引号就可以了!!我想把上面的语句做成存储过程,但不知道参数如何写!
不必客气,大家共同学习! "原来只多加一个单引号就可以了!!"----SQLSERVER规定:单引号内的单引号必须替换成一对单引号.请牢记这一规定,在构建SQL字符串时非常重要.if object_id('spTest') is not null drop proc spTest GO create proc spTest @tbName sysname, /*表名称*/ @NewValue varchar(20),/*更新的新值*/ @Where varchar(1000), /*更新条件*/ @ColOrder int /*列序号*/ as declare @ColName sysname,@sql varchar(2000) SELECT @ColName = name FROM syscolumns AS t WHERE id=object_id(@tbName) and (select count(*) from syscolumns where id = t.id and colorder <= t.colorder) = @ColOrder ----构建用于更新的SQL字符串 set @sql = 'update ' + @tbName + ' set ' + @ColName + ' = ''' + @NewValue + case when isnull(@Where,'') <> '' then ''' where ' + @Where else '''' end ----执行更新(查看SQL字符串请使用print @sql) EXEC(@sql) GO
用了hellowork(一两清风)的思路改好了 declare @a varchar(100),@n int,@i int set @n=3 select @i=colid from syscolumns A where id=object_id('a') and (select count(1) from syscolumns where id=A.id and A.colid>colid)=@n-1 select @a='update a set '+col_name(object_id('a'),@i)+'=2' exec (@a) ---改成存储过程 create proc proc_x @n int,@tab varchar(20),@newchar varchar(50) as declare @i int, @a varchar(100) select @i=colid from syscolumns A where id=object_id(@tab) and (select count(1) from syscolumns where id=A.id and A.colid>colid)=@n-1 select @a='update '+@tab+' set '+col_name(object_id('a'),@i)+'='''+@newchar+'''' exec (@a)exec proc_x 2,'a','ABC' ------------------------@n第几列,表名@tab,字符字段改成的内容@newchar,其它类型自写 exec换成print 得到:update a set a3='ABC' a3是删除a2后得的第二列
---改成存储过程 create proc proc_x @n int,@tab varchar(20),@newchar varchar(50) as declare @i int, @a varchar(100) select @i=colid from syscolumns A where id=object_id(@tab) and (select count(1) from syscolumns where id=A.id and A.colid>colid)=@n-1 select @a='update '+@tab+' set '+col_name(object_id('a'),@i)+'='''+@newchar+'''' exec (@a) 调用的时候: exec proc_x 2,'HUORS','ABC'其中'HUORS'是我自己建的表,里面的字段都是varchar 但在查询分析器里运行上面一句代码exec proc_x 2,'HUORS','ABC'提示“列名b 无效”,请问这是什么原因呢?
set @n=5 ---自定
select @a='update a set '+col_name(object_id('a'),@n)+'=1'
exec (@a)
set @n=5 ---自定
select @a='update a set '+col_name(object_id('a'),@n)+'=1'---a是表名
exec (@a)
insert test select 1,'a',3
union all select 4,'b',6select * from test
declare @col varchar(20)--指定第二列
select @col=name from syscolumns where id=object_id('test') and colid=2
exec('update test set '+@col+'=''c''')select * from testdrop table test
drop table tbTest
GO
create table tbTest(id int,col1 varchar(10),col2 varchar(10),col3 varchar(10))
insert tbTest
select 1,'a','b','c' union all
select 2,'a','b','c' union all
select 3,'a','b','c'
GOdeclare @ColName sysname,@sql varchar(2000)
declare @n int
set @n = 3 /*更新第三列*/
SELECT @ColName = name FROM syscolumns AS t WHERE id=object_id('tbtest')
and (select count(*) from syscolumns where id = t.id and colorder <= t.colorder) = @n
----构建用于更新的SQL字符串
set @sql = 'update tbTest set ' + @ColName + ' = ''1'' where 1=1'
----执行更新
EXEC(@sql)----查看
select * from tbTest
----清除测试环境
drop table tbTest/*结果
id col1 col2 col3
1 a 1 c
2 a 1 c
3 a 1 c
*/
:set @n=3
select @a='update a set '+col_name(object_id('a'),@n)+'=hi'---a是表名
exec (@a)
不要直接使用syscolumns表中的colid或colorder,因为从表中删除一个中间列时,colid和colorder并不会自动重新从1开始递增排列.
例如从上面回复的测试表tbTest中删除第三列col2列时,其后的col3列的colid和colorder值仍然是4而不是3.
例如删除了第2列,则使用COL_NAME ( table_id ,2)时就会返回NULL值.
wgzaaa() 的回答我觉得只差一点点而已,如何才能修改成其它的字符串啊?
set @n=1---自定
select @a='update a set '+col_name(object_id('a'),@n)+'=''a'''---a是表名
exec (@a)原来只多加一个单引号就可以了!!我想把上面的语句做成存储过程,但不知道参数如何写!
"原来只多加一个单引号就可以了!!"----SQLSERVER规定:单引号内的单引号必须替换成一对单引号.请牢记这一规定,在构建SQL字符串时非常重要.if object_id('spTest') is not null
drop proc spTest
GO
create proc spTest
@tbName sysname, /*表名称*/
@NewValue varchar(20),/*更新的新值*/
@Where varchar(1000), /*更新条件*/
@ColOrder int /*列序号*/
as
declare @ColName sysname,@sql varchar(2000)
SELECT @ColName = name FROM syscolumns AS t WHERE id=object_id(@tbName)
and (select count(*) from syscolumns where id = t.id and colorder <= t.colorder) = @ColOrder
----构建用于更新的SQL字符串
set @sql = 'update ' + @tbName + ' set ' + @ColName + ' = ''' + @NewValue +
case when isnull(@Where,'') <> '' then ''' where ' + @Where else '''' end
----执行更新(查看SQL字符串请使用print @sql)
EXEC(@sql)
GO
declare @a varchar(100),@n int,@i int
set @n=3
select @i=colid from syscolumns A where id=object_id('a')
and (select count(1) from syscolumns where id=A.id and A.colid>colid)=@n-1
select @a='update a set '+col_name(object_id('a'),@i)+'=2'
exec (@a)
---改成存储过程
create proc proc_x @n int,@tab varchar(20),@newchar varchar(50) as
declare @i int, @a varchar(100)
select @i=colid from syscolumns A where id=object_id(@tab)
and (select count(1) from syscolumns where id=A.id and A.colid>colid)=@n-1
select @a='update '+@tab+' set '+col_name(object_id('a'),@i)+'='''+@newchar+''''
exec (@a)exec proc_x 2,'a','ABC'
------------------------@n第几列,表名@tab,字符字段改成的内容@newchar,其它类型自写
exec换成print 得到:update a set a3='ABC' a3是删除a2后得的第二列
create proc proc_x @n int,@tab varchar(20),@newchar varchar(50) as
declare @i int, @a varchar(100)
select @i=colid from syscolumns A where id=object_id(@tab)
and (select count(1) from syscolumns where id=A.id and A.colid>colid)=@n-1
select @a='update '+@tab+' set '+col_name(object_id('a'),@i)+'='''+@newchar+''''
exec (@a)
调用的时候:
exec proc_x 2,'HUORS','ABC'其中'HUORS'是我自己建的表,里面的字段都是varchar
但在查询分析器里运行上面一句代码exec proc_x 2,'HUORS','ABC'提示“列名b 无效”,请问这是什么原因呢?