存儲過程一般用事務處理﹐當然這個事務都要自己寫的 如﹕CREATE PROC dbo.procInsert (@orderNo varchar(4),@orderName varchar(20),@orderNum numeric(10,2)) AS BEGIN TRAN SET NOCOUNT ON SET ANSI_WARNINGS OFFINSERT INTO dbo.TabX(order_no,order_name,order_num) VALUES(@OrderNo,@OrderName,@OrderNum) IF @@ERROR<>0 GOTO errhandleINSERT INTO opendatasource('sqloledb','data source=sprogram;user ID=develop;Password=12345').數據庫名.dbo.TabY(order_no,order_name,order_num) VALUES(@OrderNo,@OrderName,@OrderNum) IF @@ERROR<>0 GOTO errhandle COMMIT TRAN SELECT 1errhandle: IF @@ERROR<>0 BEGIN ROLLBACK TRAN SELECT 0 ENDGO--成功返回1,失敗返回0
如﹕CREATE PROC dbo.procInsert
(@orderNo varchar(4),@orderName varchar(20),@orderNum numeric(10,2))
AS
BEGIN TRAN
SET NOCOUNT ON
SET ANSI_WARNINGS OFFINSERT INTO dbo.TabX(order_no,order_name,order_num)
VALUES(@OrderNo,@OrderName,@OrderNum)
IF @@ERROR<>0 GOTO errhandleINSERT INTO opendatasource('sqloledb','data source=sprogram;user ID=develop;Password=12345').數據庫名.dbo.TabY(order_no,order_name,order_num)
VALUES(@OrderNo,@OrderName,@OrderNum)
IF @@ERROR<>0 GOTO errhandle
COMMIT TRAN
SELECT 1errhandle:
IF @@ERROR<>0
BEGIN
ROLLBACK TRAN
SELECT 0
ENDGO--成功返回1,失敗返回0
在存储过程退出时,存储过程结束一个事务 ,将@@TRANCOUNT=@@TRANCOUNT-1;(不论存储过程中有几个事务都是一样的)
若在存储过程中再加入事务的话,则又运行一次@@TRANCOUNT=@@TRANCOUNT+1