if exists(select 1 from syscolumns where name = '@case' and id = object_id('表名称')) exec('update 表名称 set ' + @case + ' = ' + @case + ' + 1') else update 表名称 set other = other + 1
访问SELECT * FROM syscolumns WHERE id = OBJECT_ID('表名') AND name = '列名'
aaa,bbb,ccc,ddd,other 字段名还是字窜啊 ~~~ 看不懂说什么
if object_id('test') is not null drop table test go select 0 as aaa, 0 as bbb, 0 as ccc, 0 as ddd, 0 as other into test go if object_id('testproc') is not null drop procedure test go create procedure testproc(@case varchar(10)) as begin if exists(select 1 from syscolumns where name = @case and id = object_id('test')) exec('update test set ' + @case + ' = ' + @case + ' + 1') else update test set other = other + 1 end go exec testproc 'aaa' exec testproc 'bbb' exec testproc 'ccc' exec testproc 'ddd' exec testproc 'eee' exec testproc 'eee' select * from test /* aaa bbb ccc ddd other 1 1 1 1 2 */ go drop procedure testproc drop table test
if object_id('test') is not null drop table test go select 0 as aaa, 0 as bbb, 0 as ccc, 0 as ddd, 0 as other into test go if object_id('testproc') is not null drop procedure test go create procedure testproc(@case varchar(10)) as begin if exists(select 1 from syscolumns where name = @case and id = object_id('test')) exec('update test set ' + @case + ' = ' + @case + ' + 1') else update test set other = other + 1 end go exec testproc 'aaa' exec testproc 'bbb' exec testproc 'ccc' exec testproc 'ddd' exec testproc 'eee' exec testproc 'fff' select * from test /* aaa bbb ccc ddd other 1 1 1 1 2 */ go drop procedure testproc drop table test
XZ 你快点哦 呵呵~ dulei115的不错
一个问题,如果在我的字段里如 aaa 或 bbb中出现".",这种方法还行吗?
加[] create procedure testproc(@case varchar(10)) as begin if exists(select 1 from syscolumns where name = @case and id = object_id('test')) exec('update test set [' + @case + '] = [' + @case + '] + 1') else update test set other = other + 1 end
直接加[]有问题,当@case中包含[ 或者 ] 时会出错,用函数quotename create procedure testproc(@case varchar(10)) as begin if exists(select 1 from syscolumns where name = @case and id = object_id('test')) exec('update test set ' + quotename(@case) + ' = ' + quotename(@case) + ' + 1') else update test set other = other + 1 end
exec('update 表名称 set ' + @case + ' = ' + @case + ' + 1')
else
update 表名称 set other = other + 1
go
select 0 as aaa, 0 as bbb, 0 as ccc, 0 as ddd, 0 as other
into test
go
if object_id('testproc') is not null drop procedure test
go
create procedure testproc(@case varchar(10))
as
begin
if exists(select 1 from syscolumns where name = @case and id = object_id('test'))
exec('update test set ' + @case + ' = ' + @case + ' + 1')
else
update test set other = other + 1
end
go
exec testproc 'aaa'
exec testproc 'bbb'
exec testproc 'ccc'
exec testproc 'ddd'
exec testproc 'eee'
exec testproc 'eee'
select * from test
/*
aaa bbb ccc ddd other
1 1 1 1 2
*/
go
drop procedure testproc
drop table test
go
select 0 as aaa, 0 as bbb, 0 as ccc, 0 as ddd, 0 as other
into test
go
if object_id('testproc') is not null drop procedure test
go
create procedure testproc(@case varchar(10))
as
begin
if exists(select 1 from syscolumns where name = @case and id = object_id('test'))
exec('update test set ' + @case + ' = ' + @case + ' + 1')
else
update test set other = other + 1
end
go
exec testproc 'aaa'
exec testproc 'bbb'
exec testproc 'ccc'
exec testproc 'ddd'
exec testproc 'eee'
exec testproc 'fff'
select * from test
/*
aaa bbb ccc ddd other
1 1 1 1 2
*/
go
drop procedure testproc
drop table test
create procedure testproc(@case varchar(10))
as
begin
if exists(select 1 from syscolumns where name = @case and id = object_id('test'))
exec('update test set [' + @case + '] = [' + @case + '] + 1')
else
update test set other = other + 1
end
create procedure testproc(@case varchar(10))
as
begin
if exists(select 1 from syscolumns where name = @case and id = object_id('test'))
exec('update test set ' + quotename(@case) + ' = ' + quotename(@case) + ' + 1')
else
update test set other = other + 1
end