要求使用该守则的客户受到限制,只能处理储存的代用品的返回代码。 因此,我们修改了我们关于错误和default<>>>至的通常合同,如果无错误,则将予以修改。
如果该守则击中了内层鱼群,那么ReTURN代码的缺省为4,而后为0。
是否有任何人知道这来自什么? 参考
Cheers gbn
IF OBJECT_ID( dbo.foo ) IS NOT NULL DROP TABLE dbo.foo
GO
CREATE TABLE dbo.foo (
KeyCol char(12) NOT NULL,
ValueCol xml NOT NULL,
Comment varchar(1000) NULL,
CONSTRAINT PK_foo PRIMARY KEY CLUSTERED (KeyCol)
)
GO
IF OBJECT_ID( dbo.bar ) IS NOT NULL DROP PROCEDURE dbo.bar
GO
CREATE PROCEDURE dbo.bar
@Key char(12),
@Value xml,
@Comment varchar(1000)
AS
SET NOCOUNT ON
DECLARE @StartTranCount tinyint;
BEGIN TRY
SELECT @StartTranCount = @@TRANCOUNT;
IF @StartTranCount = 0 BEGIN TRAN;
BEGIN TRY
--SELECT @StartTranCount = fish --generates an error and goes to outer CATCH
INSERT dbo.foo (KeyCol, ValueCol, Comment) VALUES (@Key, @Value, @Comment);
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 2627 --PK violation
UPDATE
dbo.foo
SET
ValueCol = @Value, Comment = @Comment
WHERE
KeyCol = @Key;
ELSE
RAISERROR ( Tits up , 16, 1);
END CATCH
IF @StartTranCount = 0 COMMIT TRAN;
END TRY
BEGIN CATCH
IF @StartTranCount = 0 AND XACT_STATE() <> 0 ROLLBACK TRAN;
RETURN -1
END CATCH
--Without this, we ll send -4 if we hit the UPDATE CATCH block above
--RETURN 0
GO
--please run these **separately**
--Run with RETURN 0 and fish line commented out
DECLARE @rtn int
EXEC @rtn = dbo.bar abcdefghijkl , <foobar /> , testing
SELECT @rtn; SELECT * FROM dbo.foo
GO
DECLARE @rtn int
EXEC @rtn = dbo.bar abcdefghijkl , <foobar2 /> , testing2
--updated OK but we get @rtn = -4
SELECT @rtn; SELECT * FROM dbo.foo
GO
--uncomment fish line
DECLARE @rtn int
EXEC @rtn = dbo.bar abcdefghijkl , <foobar /> , testing
--Hit outer CATCH, @rtn = -1 as expected
SELECT @rtn; SELECT * FROM dbo.foo