SQL Server 2000:如何退出存储过程?

How can I exit in the middle of a stored procedure?

如何在存储过程中退出?

I have a stored procedure where I want to bail out early (while trying to debug it). I've tried calling RETURN and RAISERROR, and the sp keeps on running:

我有一个要尽早退出的存储过程(同时尝试调试它)。我试过调用RETURN和RAISERROR, sp继续运行:

CREATE PROCEDURE dbo.Archive_Session @SessionGUID uniqueidentifier AS

    print 'before raiserror'
    raiserror('this is a raised error', 18, 1)
    print 'before return'
    return -1
    print 'after return'

[snip]

I know it keeps running because I encounter an error further down. I don't see any of my prints. If I comment out the bulk of the stored procedure:

我知道它一直在运行，因为我在下面遇到了一个错误。我没有看到我的指纹。如果我将存储过程的大部分注释掉:

CREATE PROCEDURE dbo.Archive_Session @SessionGUID uniqueidentifier AS

    print 'before raiserror'
    raiserror('this is a raised error', 18, 1)
    print 'before return'
    return -1
    print 'after return'

   /*
     [snip]
   */

Then I don't get my error, and I see the results:

然后我没有得到我的错误，我看到了结果:

before raiserror
Server: Msg 50000, Level 18, State 1, Procedure Archive_Session, Line 5
this is a raised error
before return

So the question is: how do I bail out of a stored procedure in SQL Server?

所以问题是:如何在SQL Server中脱离存储过程?

7 个解决方案

#1

You can use RETURN to stop execution of a stored procedure immediately. Quote taken from Books Online:

可以使用RETURN立即停止存储过程的执行。引自网上书籍:

Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed.

无条件地从查询或过程中退出。返回是立即的和完整的，并且可以在任何地方用于从过程、批处理或语句块中退出。返回后的语句不会被执行。

Out of paranoia, I tried yor example and it does output the PRINTs and does stop execution immediately.

出于偏执狂，我尝试了你的例子，它确实输出了指纹并立即停止执行。

#2

Unless you specify a severity of 20 or higher, raiserror will not stop execution. See the MSDN documentation.

除非您指定20或更高的严重性，raiserror不会停止执行。看到MSDN文档。

The normal workaround is to include a return after every raiserror:

通常的解决方法是在每一个raiserror之后包含一个return:

if @whoops = 1
    begin
    raiserror('Whoops!', 18, 1)
    return -1
    end

#3

Put it in a TRY/CATCH.

试试看。

When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block

当RAISERROR在TRY块中以11或更高的严重程度运行时，它将控制转移到相关的CATCH块

Reference: MSDN.

参考:MSDN。

EDIT: This works for MSSQL 2005+, but I see that you now have clarified that you are working on MSSQL 2000. I'll leave this here for reference.

编辑:这适用于MSSQL 2005+，但是我看到您已经澄清了您正在研究MSSQL 2000。我把这个放在这里作为参考。

#4

i figured out why RETURN is not unconditionally returning from the stored procedure. The error i'm seeing is while the stored procedure is being compiled - not when it's being executed.

我理解了为什么返回不是无条件地从存储过程返回。我看到的错误是在编译存储过程时，而不是在执行过程时。

Consider an imaginary stored procedure:

考虑一个虚构的存储过程:

CREATE PROCEDURE dbo.foo AS

INSERT INTO ExistingTable
EXECUTE LinkedServer.Database.dbo.SomeProcedure

Even though this stord proedure contains an error (maybe it's because the objects have a differnet number of columns, maybe there is a timestamp column in the table, maybe the stored procedure doesn't exist), you can still save it. You can save it because you're referencing a linked server.

即使这个stord proedure包含一个错误(可能是因为对象的列数不同，可能表中有一个时间戳列，或者存储过程不存在)，您仍然可以保存它。您可以保存它，因为您正在引用一个链接服务器。

But when you actually execute the stored procedure, SQL Server then compiles it, and generates a query plan.

但是当您实际执行存储过程时，SQL Server会编译它，并生成查询计划。

My error is not happening on line 114, it is on line 114. SQL Server cannot compile the stored procedure, that's why it's failing.

我的错误不在114行，而是在114行。SQL Server不能编译存储过程，这就是它失败的原因。

And that's why RETURN does not return, because it hasn't even started yet.

这就是为什么返回不会返回，因为它还没有开始。

#5

This works over here.

这里的工作。

ALTER PROCEDURE dbo.Archive_Session
    @SessionGUID int
AS 
    BEGIN
        SET NOCOUNT ON
        PRINT 'before raiserror'
        RAISERROR('this is a raised error', 18, 1)
        IF @@Error != 0 
            RETURN
        PRINT 'before return'
        RETURN -1
        PRINT 'after return'
    END
go

EXECUTE dbo.Archive_Session @SessionGUID = 1

Returns

before raiserror
Msg 50000, Level 18, State 1, Procedure Archive_Session, Line 7
this is a raised error

#6

This seems like a lot of code but the best way i've found to do it.

这看起来像很多代码，但这是我找到的最好的方法。

    ALTER PROCEDURE Procedure
    AS

    BEGIN TRY
        EXEC AnotherProcedure
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );
        RETURN --this forces it out
    END CATCH

--Stuff here that you do not want to execute if the above failed.    

    END --end procedure

#7

Its because you have no BEGIN and END statements. You shouldn't be seeing the prints, or errors running this statement, only Statement Completed (or something like that).

因为没有开始和结束语句。您不应该看到输出或运行此语句的错误，只看到已完成的语句(或类似的内容)。

#1