SQL Server存储过程返回代码奇怪

Question

调用此代码的客户端受到限制，只能处理来自存储的特定代码的返回代码。所以，我们修改我们平常合同RETURN -1上的错误和默认到RETURN 0如果没有错误

如果代码打内catch块，然后返回码默认值是-4而不是0

有谁知道这是从哪里来的？参考

干杯 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 

Answer 1

与程序玩弄，我可以得到回报-6，如果我插入一个空到foo.KeyCol并取出RAISERROR的内抓住。这是SQL Server正在做的事情，这里记录：Return Values from Stored Procedures。