代码
create proc DemandInfoInsert
@Type bigint,
@DemandSort bigint, 
@Title varchar(100),
@detail text,
@EndDate datetime,
@AddUser bigint,
@IsShow int,
@AddDate datetime,
@ServeSort bigint,
@TransitSort bigint,
@GoodsSort bigint,
@GoodsWeight decimal,
@Unit varchar(20),
@PackExplain varchar(64),
@WillPrice money,
@StartPlace varchar(100),
@FinishPlace varchar(100),
@ConsignmentDate datetime,
@LinkMan varchar(8),
@LinkAddress varchar(100),
@PostNum varchar(8),
@LinkPhone varchar(20),
@Email varchar(50),
@Fax varchar(20),
@IsType bigint
AS 
Begin
   begin transaction AddDemandInfo
    declare @DemandInfoID int
    insert into DemandInfo (Type,DemandSort,Title,detail,EndDate,AddUser,IsShow,AddDate) values(@Type,@DemandSort,@Title,@detail,@EndDate,@AddUser,@IsShow,@AddDate)
    set @DemandInfoID = scope_identity()
    insert into DemandSubInfo (DemandID,ServeSort,TransitSort,GoodsSort,GoodsWeight,Unit,PackExplain,WillPrice,StartPlace,FinishPlace,ConsignmentDate) values(@DemandInfoID,@ServeSort,@TransitSort,@GoodsSort,@GoodsWeight,@Unit,@PackExplain,@WillPrice,@StartPlace,@FinishPlace,@ConsignmentDate)
    insert into TradeLinkInfo (LinkMan,LinkAddress,PostNum,LinkPhone,Email,Fax,PyTradeID,IsType) values(@LinkMan,@LinkAddress,@PostNum,@LinkPhone,@Email,@Fax,@DemandInfoID,@IsType)
    if @@Error<>0
    begin
     Rollback transaction AddDemandInfo
    end
   commit transaction AddDemandInfo
End
GO
exec DemandInfoInsert 1,2,'dd','ss','2008-08-14',1,0,'2008-09-14',2,3,1,122,'吨','ddd',112.02,'武汉','上海','2008-09-16','zf','dfasfdaf','12355656','23423424','dj@123.com','2123123123',1





protected void Button1_Click(object sender, EventArgs e) 
{ 
SqlConnection conn; 
SqlCommand cmd1, cmd2; 
SqlTransaction tra; 
conn = new SqlConnection("server=.;database=test;uid=sa;pwd=123"); 
conn.Open(); 
tra = conn.BeginTransaction();//开始事务 
cmd1 = new SqlCommand("update Test set StuName='佳儿' where StuScore=111", conn); 
cmd2 = new SqlCommand("update Test set StuName='fdsafdsafdsfdsafsfsdadsadsadfsafsfsfdf' where StuScore=222", conn); 
try 
{ 
cmd1.ExecuteNonQuery();//此句可以成功执行 
cmd2.ExecuteNonQuery();//此句故意让其执行失败 
tra.Commit();//无异常，提交事务(两句执行都有效) 
Response.Write("<script>alert('执行成功')</script>"); 
} 
catch 
{ 
tra.Rollback();//出现异常，回滚事务(两句执行都无效) 
Response.Write("<script>alert('执行失败')</script>"); 
} 
finally 
{ 
conn.Close(); 
} 
}