27,579
社区成员
发帖
与我相关
我的任务
分享
create proc proc_tranDemo
@tablename1 nvarchar(50),
@imagepwd nvarchar(20),
@imageName nvarchar(50),
@imageBigname nvarchar(50),
@tablename2 nvarchar(50),
@name nvarchar(20),
@phone nvarchar(30),
@openid nvarchar(30)
as
declare @err int
set @err = 0
begin TRANSACTION --事物开始
BEGIN TRY
DECLARE @str nVARCHAR(2000)
set @str='update '+@tablename1+' set [Status]=1 where ImagePwd='+@imagepwd+' and ImageName='+@imageName+' and ImageBigname='+@imageBigname+''
execute sp_executesql @str
set @err = @err + @@error
DECLARE @strs nVARCHAR(2000)
set @strs='insert into '+@tablename2+' (name,phone,openid,ImageName,Imagepwd,[Status],ImageBigname) values('+@name+','+@phone+','+@openid+','+@imageName+','+@imagepwd+',0,'+@imageBigname+')'
execute sp_executesql @strs
set @err = @err + @@error
end try
BEGIN CATCH
PRINT '出现异常,错误编号:' + convert(varchar,error_number()) + ',错误消息:' + error_message()
SET @err = @err + 1
END CATCH
if(@err>0)
begin
rollback TRANSACTION --错误回滚事物
end
else
begin
commit TRANSACTION --无错误提交事物
end
print @err
--拼动态SQL时,字符串值要加引号。其余一样处理
set @str='update '+@tablename1+' set [Status]=1 where ImagePwd='''+@imagepwd+''' and ...'