/*本存储过程用来添加个人注册用户的基本用户信息*/
CREATE PROCEDURE dbo.ActiveUsers
(
@SPUserId int,
@UserName nvarchar(50),
@EMail nvarchar(100),
@TranferNumber nvarchar(50),
@XHNumber nvarchar(50),
@UserKind char(10),
@Resource tinyint
)
AS insert into TB_Users(UserName,UserKind,SPId)
VALUES (@UserName,@UserKind,@SPUserId) DECLARE @UserId int
SET @UserId = (select UserId from TB_Users where UserName = @UserName)
insert into TB_PersonInfo(EMail,UserId)
VALUES (@EMail,@UserId) UPDATE TB_XHNumber
SET TransferPhoneNumber = @TranferNumber,SetStatus = 3,Resource = @Resource,UserId = @UserId
where XHNumber = @XHNumber AND SetStatus = 0
RETURN GO
---------------------
上面是把几个SQL语句弄到了一个存储过程里面了,我在.NET里面执行它的时候,却发现有的时候,有的时候前两个执行成功,后一个执行失败,而我想得到的是,要么都成功,要么失败就回滚,可是我对事务不熟悉,所以请教各位大哥~
CREATE PROCEDURE dbo.ActiveUsers
(
@SPUserId int,
@UserName nvarchar(50),
@EMail nvarchar(100),
@TranferNumber nvarchar(50),
@XHNumber nvarchar(50),
@UserKind char(10),
@Resource tinyint
)
AS insert into TB_Users(UserName,UserKind,SPId)
VALUES (@UserName,@UserKind,@SPUserId) DECLARE @UserId int
SET @UserId = (select UserId from TB_Users where UserName = @UserName)
insert into TB_PersonInfo(EMail,UserId)
VALUES (@EMail,@UserId) UPDATE TB_XHNumber
SET TransferPhoneNumber = @TranferNumber,SetStatus = 3,Resource = @Resource,UserId = @UserId
where XHNumber = @XHNumber AND SetStatus = 0
RETURN GO
---------------------
上面是把几个SQL语句弄到了一个存储过程里面了,我在.NET里面执行它的时候,却发现有的时候,有的时候前两个执行成功,后一个执行失败,而我想得到的是,要么都成功,要么失败就回滚,可是我对事务不熟悉,所以请教各位大哥~
解决方案 »
- 怎么循环一个表的列名
- JSP中为utf-8,数据库中全部是乱码,请求帮忙解决
- 新手问题,要复制.mdf文件,是不是要停止某些sql服务
- 关于把查找的数据怎样插入临时表里!菜鸟问题!
- ==========这个sql语句如何写?
- 同一字段相同值的记录最多显示3条记录
- 小弟求救!!!!拨号网络中远程访问数据库出错!!!!!急急急!!!!在线等待
- SQL SERVER的分布式开发应看什么方面的内容?顺带散点分
- 请问:数据库从DBASE到SQL,怎样用SQL语句或程序进行转化呢?还有转化DBASE时怎样使用GROUP运算呀???
- 关于SQL SERVER的题目
- 再次问字符串比较问题.急...........!!!!!!!!!!!!!!!!!!!!!!!!!!1
- 求sql
begin
begin tran
insert table1 ...
insert table2 ...
commit tran
endCREATE PROCEDURE dbo.ActiveUsers
(
@SPUserId int,
@UserName nvarchar(50),
@EMail nvarchar(100),
@TranferNumber nvarchar(50),
@XHNumber nvarchar(50),
@UserKind char(10),
@Resource tinyint
)
AS
set xact_abort on begin tran insert into TB_Users(UserName,UserKind,SPId)
VALUES (@UserName,@UserKind,@SPUserId) DECLARE @UserId int
SET @UserId = (select UserId from TB_Users where UserName = @UserName)
insert into TB_PersonInfo(EMail,UserId)
VALUES (@EMail,@UserId) UPDATE TB_XHNumber
SET TransferPhoneNumber = @TranferNumber,SetStatus = 3,Resource = @Resource,UserId = @UserId
where XHNumber = @XHNumber AND SetStatus = 0
RETURN
commit tranGO