在SQL存储过程中使用的RETURN，在存储过程相互调用时，到底跳向了哪里？为了弄清这个问题，我写了以下三个存储过程来进行测试。
一、FFatherProcedure
一、FFatherProcedure
1
CREATE PROCEDURE FFatherProcedure
2 @RetCode INT OUTPUT
3AS
4
5BEGIN
6SET @RetCode=0;
7SELECT @RetCode
8RETURN
9END
CREATE PROCEDURE FFatherProcedure2
@RetCode INT OUTPUT3
AS4
5
BEGIN6
SET @RetCode=0;7
SELECT @RetCode8
RETURN9
END二、FSonProcedure1
1CREATE PROCEDURE FSonProcedure1
2 @RetCode INT OUTPUT
3AS
4
5BEGIN
6SET @RetCode=1;
7SELECT @RetCode
8RETURN
9END
CREATE PROCEDURE FSonProcedure12
@RetCode INT OUTPUT3
AS4
5
BEGIN6
SET @RetCode=1;7
SELECT @RetCode8
RETURN9
END三、FSonProcedure2
1CREATE PROCEDURE FSonProcedure2
2 @RetCode INT OUTPUT
3AS
4
5BEGIN
6SET @RetCode=2;
7SELECT @RetCode
8RETURN
9END
CREATE PROCEDURE FSonProcedure22
@RetCode INT OUTPUT3
AS4
5
BEGIN6
SET @RetCode=2;7
SELECT @RetCode8
RETURN9
END先在查询分析器中执行这三个存储过程。
1DECLARE @RetCode INT
2
3EXECUTE FFatherProcedure @RetCode OUTPUT
4EXECUTE FSonProcedure1 @RetCode OUTPUT
5EXECUTE FSonProcedure2 @RetCode OUTPUT
DECLARE @RetCode INT2
3
EXECUTE FFatherProcedure @RetCode OUTPUT4
EXECUTE FSonProcedure1 @RetCode OUTPUT5
EXECUTE FSonProcedure2 @RetCode OUTPUT返回的结果分别为0,1,2。
下面修改FFatherProcedure，然后执行。
1set ANSI_NULLS ON
2set QUOTED_IDENTIFIER ON
3go
4
5ALTER PROCEDURE [dbo].[FFatherProcedure]
6 @RetCode INT OUTPUT
7AS
8
9BEGIN
10SET @RetCode=0;
11EXECUTE FSonProcedure1 @RetCode OUTPUT
12EXECUTE FSonProcedure2 @RetCode OUTPUT
13SELECT @RetCode
14END
set ANSI_NULLS ON2
set QUOTED_IDENTIFIER ON3
go4
5
ALTER PROCEDURE [dbo].[FFatherProcedure]6
@RetCode INT OUTPUT7
AS8
9
BEGIN10
SET @RetCode=0;11
EXECUTE FSonProcedure1 @RetCode OUTPUT12
EXECUTE FSonProcedure2 @RetCode OUTPUT13
SELECT @RetCode14
END
1DECLARE @RetCode INT
2
3EXECUTE FFatherProcedure @RetCode OUTPUT
DECLARE @RetCode INT2
3
EXECUTE FFatherProcedure @RetCode OUTPUT返回的结果是1,1。
再修改FFatherProcedure为：
1set ANSI_NULLS ON
2set QUOTED_IDENTIFIER ON
3go
4
5ALTER PROCEDURE [dbo].[FFatherProcedure]
6 @RetCode INT OUTPUT
7AS
8
9BEGIN
10SET @RetCode=0;
11EXECUTE FSonProcedure1 @RetCode OUTPUT
12EXECUTE FSonProcedure2 @RetCode OUTPUT
13SELECT @RetCode
14END
set ANSI_NULLS ON2
set QUOTED_IDENTIFIER ON3
go4
5
ALTER PROCEDURE [dbo].[FFatherProcedure]6
@RetCode INT OUTPUT7
AS8
9
BEGIN10
SET @RetCode=0;11
EXECUTE FSonProcedure1 @RetCode OUTPUT12
EXECUTE FSonProcedure2 @RetCode OUTPUT13
SELECT @RetCode14
END再执行，返回的结果为1,2,2。这是因为在执行过存储过程FSonProcedure1之后又执行存储过程FSonProcedure2，@RetCode的值被FSonProcedure2覆盖了。再修改FFatherProcedure为：
1set ANSI_NULLS ON
2set QUOTED_IDENTIFIER ON
3go
4
5ALTER PROCEDURE [dbo].[FFatherProcedure]
6 @RetCode INT OUTPUT
7AS
8
9BEGIN
10SET @RetCode=0;
11EXECUTE FSonProcedure2 @RetCode OUTPUT
12EXECUTE FSonProcedure1 @RetCode OUTPUT
13SELECT @RetCode
14END
set ANSI_NULLS ON2
set QUOTED_IDENTIFIER ON3
go4
5
ALTER PROCEDURE [dbo].[FFatherProcedure]6
@RetCode INT OUTPUT7
AS8
9
BEGIN10
SET @RetCode=0;11
EXECUTE FSonProcedure2 @RetCode OUTPUT12
EXECUTE FSonProcedure1 @RetCode OUTPUT13
SELECT @RetCode14
END返回的结果是2,1,1。
再修改FFatherProcedure为：
1set ANSI_NULLS ON
2set QUOTED_IDENTIFIER ON
3go
4
5ALTER PROCEDURE [dbo].[FFatherProcedure]
6 @RetCode INT OUTPUT
7AS
8
9BEGIN
10SET @RetCode=0;
11EXECUTE FSonProcedure2 @RetCode OUTPUT
12RETURN
13EXECUTE FSonProcedure1 @RetCode OUTPUT
14SELECT @RetCode
15END
set ANSI_NULLS ON2
set QUOTED_IDENTIFIER ON3
go4
5
ALTER PROCEDURE [dbo].[FFatherProcedure]6
@RetCode INT OUTPUT7
AS8
9
BEGIN10
SET @RetCode=0;11
EXECUTE FSonProcedure2 @RetCode OUTPUT12
RETURN13
EXECUTE FSonProcedure1 @RetCode OUTPUT14
SELECT @RetCode15
END返回的结果值为2。
到此而看，存储过程中的RETURN，仅仅是直接跳出当前所在存储过程而已，他还是忠实地把RETURN前所设置的值返回给调用他的父存储过程。而在父存储过程中，如果不使用GOTO语句，会按照顺序逐一赋值，最后处理的结果会覆盖前面的结果。
浙公网安备 33010602011771号