SQL存储过程创建及事务处理

基本语法：

Code

CREATE PROC SP_INSERT_CUser
@UserName NVARCHAR ( 50 ),
@Name NVARCHAR ( 50 ),
@TelPhone NVARCHAR ( 50 ),
@OperateTime DATETIME ,
@IDType INT ,
@IDNumber NVARCHAR ( 50 ),
@CompanyName NVARCHAR ( 50 ),
@CompanyType INT
AS
BEGIN
DECLARE @PId INT
BEGIN TRANSACTION
-- 插入前台用户
INSERT INTO T_CUser(UserName,Name,TelPhone,OperateTime,IDType,IDNumber,CompanyName,CompanyType) VALUES ( @UserName , @Name , @TelPhone , @OperateTime , @IDType , @IDNumber , @CompanyName , @CompanyType )

-- 获取客户权限ID
SELECT @PId = PId FROM T_Part WHERE CHARINDEX ( ' 客户 ' ,Name) <> 0

-- 插入后台用户
INSERT INTO T_User(PId,UserName) VALUES ( @PId , @UserName )

IF @@Error > 0 OR @PId IS NULL
BEGIN
Rollback TRANSACTION
END
ELSE
COMMIT TRANSACTION
END
GO

转载于:https://www.cnblogs.com/zerowang/archive/2011/04/28/2031528.html