谢谢查询分析器 里面运行有错。,SQL 2000
create procedure procRetity
@tablename varchar(20), --表名
@colum varchar(20), --用来识别和判断自动标识的列(最好是主键)
@cum varchar(20) --原来旧列(需要重新编辑的列)
as
declare @getTableName varchar(20); --获取输入进来的表名
declare @error int; --用来存储错误数
set @getTableName=@tablename; --给变量赋值
begin transaction; select identity(int,1,1) num_id,@colum into #tmp from @getTableName; set @error=@error+@@error; update @getTableName set @getTableName.@cum=#tmp.num_id from #tmp where @getTableName.@colum=#tmp.@colum; set @error=@error+@@error;
if(@error=0)
commit transaction;
else
rollback transactio
create procedure procRetity
@tablename varchar(20), --表名
@colum varchar(20), --用来识别和判断自动标识的列(最好是主键)
@cum varchar(20) --原来旧列(需要重新编辑的列)
as
declare @getTableName varchar(20); --获取输入进来的表名
declare @error int; --用来存储错误数
set @getTableName=@tablename; --给变量赋值
begin transaction; select identity(int,1,1) num_id,@colum into #tmp from @getTableName; set @error=@error+@@error; update @getTableName set @getTableName.@cum=#tmp.num_id from #tmp where @getTableName.@colum=#tmp.@colum; set @error=@error+@@error;
if(@error=0)
commit transaction;
else
rollback transactio
create procedure procRetity
@getTableName varchar(20), --表名
@colum varchar(20), --用来识别和判断自动标识的列(最好是主键)
@cum varchar(20) --原来旧列(需要重新编辑的列)
as
declare @error int --用来存储错误数
declare @tru nvarchar(3000)
begin transaction
select @tru = 'select identity(int,1,1) num_id,@colum into #tmp from '+@getTableName+''
exec(@tru)
set @error=@error+@@error
select @tru = 'update '+@getTableName+' set '+@cum+'=#tmp.num_id from #tmp where '+@colum+'=#tmp.@colum'
exec(@tru)
set @error=@error+@@error
if(@error=0)
commit transaction
else
rollback transaction
@tablename varchar(20), --表名
@colum varchar(20), --用来识别和判断自动标识的列(最好是主键)
@cum varchar(20) --原来旧列(需要重新编辑的列)
as
declare @getTableName varchar(20); --获取输入进来的表名
declare @error int; --用来存储错误数
set @getTableName=@tablename; --给变量赋值
exec('select identity(int,1,1) num_id,'+@colum+' into ##tmp from '+@getTableName)
exec('update '+@getTableName+' set '+@cum+'=##tmp.num_id from ##tmp where '+@getTableName+'.'+@colum+'=##tmp.'+@colum+'; drop table ##tmp')