请问我这样写存储过程，如果执行不成功会回滚吗？
请问我这样写存储过程，如果执行不成功会回滚吗？如果不能回滚，该如何写这个SQL语句呢？请各位不吝赐教
CREATE proc p_insert_courtInformation
(
@ymd char(10),
@field char(4)
)
as
begin
tran
insert into field(ymd, field) values (@ymd, @field)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0630,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0637,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0644,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0651,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0658,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0705,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0712,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0719,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0726,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0733,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0740,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0747,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0754,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0801,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0808,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0815,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0822,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0829,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0836,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0843,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0850,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0857,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0904,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0911,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0918,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0925,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0932,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0939,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0946,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,0953,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1000,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1007,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1014,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1021,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1028,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1035,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1042,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1049,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1056,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1103,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1110,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1117,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1124,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1131,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1138,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1145,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1152,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1159,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1206,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1213,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1220,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1227,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1234,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1241,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1248,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1255,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1302,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1309,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1316,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1323,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1330,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1337,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1344,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1351,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1358,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1405,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1412,0)
insert into courtInformation(ymd,timepoint,bookcount) values (@ymd,1419,0)
commit tran
return
GO
[解决办法]
--按如下处理会回滚：
create proc sp_Test
as
--设置，如果事务中的任何代码出错，则整个事务回滚。
set xact_abort on
begin tran
....
commit tran
return
[解决办法]
貼一個聯機幫助上的例子
範例
本範例在還有其他 Transact-SQL 陳述式的交易中，造成外部索引鍵違反錯誤。在第一組陳述式中會產生錯誤，但是其他的陳述式將成功地執行，交易也會成功地認可。在第二組陳述式中，SET XACT_ABORT 設定變更為 ON，將造成陳述式錯誤，中斷批次及交易復原。
CREATE TABLE t1 (a int PRIMARY KEY)
CREATE TABLE t2 (a int REFERENCES t1(a))
GO
INSERT INTO t1 VALUES (1)
INSERT INTO t1 VALUES (3)
INSERT INTO t1 VALUES (4)
INSERT INTO t1 VALUES (6)
GO
SET XACT_ABORT OFF
GO
BEGIN TRAN
INSERT INTO t2 VALUES (1)
INSERT INTO t2 VALUES (2) /* Foreign key error */
INSERT INTO t2 VALUES (3)
COMMIT TRAN
GO
SET XACT_ABORT ON
GO
BEGIN TRAN
INSERT INTO t2 VALUES (4)
INSERT INTO t2 VALUES (5) /* Foreign key error */
INSERT INTO t2 VALUES (6)
COMMIT TRAN
GO
/* Select shows only keys 1 and 3 added.
Key 2 insert failed and was rolled back, but
XACT_ABORT was OFF and rest of transaction
succeeded.
Key 5 insert error with XACT_ABORT ON caused
all of the second transaction to roll back. */
SELECT *
FROM t2
GO
DROP TABLE t2
DROP TABLE t1
GO
