一条存过过程语句,返回奇怪的有关问题

一条存过过程语句,返回奇怪的问题


CREATE                                     PROC   [dbo].[proc_SMS_00000test]
@sNumber VARCHAR(100),
@iResult INT OUTPUT --输出参数,提示成功0或失败1
AS
BEGIN
BEGIN   TRANSACTION
--附值变量
SET   @iResult   =   0
DECLARE   @sStr VARCHAR(100)
BEGIN
SELECT   @sStr=AAACC   FROM   BBB   WHERE   vAc   =   @sNumber
IF   @@error   <>   0   GOTO   ROLL
INSERT   INTO   AAA   (vNumber)VALUES   (@sStr)
IF   @@error   <>   0   GOTO   ROLL
INSERT   INTO   AAA   (vNumber)VALUES   (@sNumber)
IF   @@error   <>   0   GOTO   ROLL
END
COMMIT   TRANSACTION
ROLL:
IF   @@TRANCOUNT   !=   0  
BEGIN
ROLLBACK   TRANSACTION
SET   @iResult   =   1
END
ELSE
SET   @iResult   =   0

END

--执行语句
DECLARE   @iResult   INT
EXEC   proc_SMS_00000test
@sNumber= '123456 '
,@iResult   =   @iResult   OUTPUT
SELECT   @iResult   AS   iResult


服务器错误多步   OLE   DB   操作产生错误。如果可能，请检查每个   OLE   DB   状态值。没有工作被完成。

------解决方案--------------------
CREATE PROC [dbo].[proc_SMS_00000test]
@sNumber VARCHAR(100),
@iResult INT OUTPUT --输出参数,提示成功0或失败1
AS
BEGIN
BEGIN TRANSACTION
--附值变量
SET @iResult = 0
DECLARE @sStr VARCHAR(100)

SELECT @sStr=AAACC FROM BBB WHERE vAc = @sNumber
IF @@error <> 0
SET @iResult = 1
INSERT INTO AAA (vNumber)VALUES (@sStr)
IF @@error <> 0
SET @iResult = 1
INSERT INTO AAA (vNumber)VALUES (@sNumber)
IF @@error <> 0
SET @iResult = 1
if @iResult = 1
COMMIT TRANSACTION
else
ROLLBACK TRANSACTION

END