if exists(select name from sysobjects where name='sp_modify_twomaterialpropcencol') drop procedure sp_modify_twomaterialpropcencol go --修改字段 create procedure sp_modify_twomaterialpropcencol ( @propen varchar(50)=null, @tabname varchar(50)=null, @propencmb varchar(50)=null ) as BEGIN TRANSACTION if not exists(SELECT * FROM syscolumns WHERE (syscolumns.id = (SELECT sysobjects.id FROM sysobjects WHERE (sysobjects.name = @tabname)) AND (syscolumns.name = @propencmb) )) begin exec(' sp_rename '' + @tabname + '' .'' + @propencmb + '' ,'' + @propen + '', 'column' ') end IF @@ERROR >0 --OR @@ROWCOUNT = 0 BEGIN RAISERROR('modify of propen failed',16,1) ROLLBACK TRANSACTION RETURN @@ERROR END COMMIT TRANSACTION RETURN 0GO就是上面这个存储过程
drop procedure sp_modify_twomaterialpropcencol
go
--修改字段
create procedure sp_modify_twomaterialpropcencol
(
@propen varchar(50)=null,
@tabname varchar(50)=null,
@propencmb varchar(50)=null
)
as
BEGIN TRANSACTION
if not exists(SELECT * FROM syscolumns WHERE (syscolumns.id = (SELECT sysobjects.id FROM sysobjects WHERE (sysobjects.name = @tabname)) AND (syscolumns.name = @propencmb) ))
begin
exec('
sp_rename '' + @tabname + '' .'' + @propencmb + '' ,'' + @propen + '', 'column'
')
end
IF @@ERROR >0 --OR @@ROWCOUNT = 0
BEGIN
RAISERROR('modify of propen failed',16,1)
ROLLBACK TRANSACTION
RETURN @@ERROR
END
COMMIT TRANSACTION
RETURN 0GO就是上面这个存储过程
sp_rename '' + @tabname + '' .'' + @propencmb + '' ,'' + @propen + '', column
')这段有错误能不能帮忙改一下!谢谢!
sp_rename [ @objname = ] 'object_name' ,
[ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]你中间怎么是两个参数,可以这样用么?
create table temp
(名称 varchar(10),
数量 int
)select * from temp修改列名declare @tablename varchar(10)
declare @columnname varchar(10)
declare @newname varchar(10)
set @tablename='temp'
set @columnname='名称'
set @newname='aa'
exec('sp_rename ''' + @tablename + '' +'.'+'' + @columnname + ''','+@newname+' ,''column''')
select * from temp
用这个
exec('sp_rename ''' + @tabname + '' +'.'+'' + @propencmb + ''' ,' + @propen + ', ''column''')
可能是你的参数问题吧,你把那些'=null'都去掉试试看
问题已经解决,刚才是我自已的一个低级失误!
谢谢 rookie_one(流氓会武术,谁都挡不住)