SQL Server 存储过程返回码奇怪
调用此代码的客户端受到限制,只能处理来自存储过程的返回代码.因此,我们将通常的合约修改为 RETURN -1
出错,default 修改为 RETURN 0
如果没有错误
The client that calls this code is restricted and can only deal with return codes from stored procs. So, we modified our usual contract to RETURN -1
on error and default to RETURN 0
if no error
如果代码命中内部 catch 块,则返回代码默认为 -4 而不是 0
If the code hits the inner catch block, then the RETURN code default is -4 rather then 0
请问有大佬知道出处吗?参考
Does anyone know where this comes from please? With reference
干杯英镑
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
推荐答案
在玩弄这个过程时,如果我在 foo.KeyCol 中插入一个 null 并删除内部 catch 中的 RAISERROR,我可以得到一个返回值 -6.这是 SQL Server 正在做的事情,并记录在此处:Return Values from Stored Procedures.
In playing around with the procedure, I can get a a return -6, if I insert a null into foo.KeyCol and remove the RAISERROR in the inner catch. This is something SQL Server is doing, and is documented here: Return Values from Stored Procedures.
相关文章