To invoke another stored procedure in one of the stored procedures, you can invoke one of the following two ways:
EXECUTE <欲执行之预存程序的名称>
Or
EXEC <欲执行之预存程序的名称>
If the name of the stored procedure being invoked has a return value, and you need to receive the return value, you must use the OUTPUT keyword to enable the invoked stored procedure to take back the value when executing the stored procedure you want to execute. Only you must first use the OUTPUT keyword to create the data type of the numeric value to be returned in the invoked stored procedure. As shown below, this is an example:
CREATE PROCEDURE #欢迎 @strInput NVARCHAR(50) OUTPUT
AS
BEGIN
SET @strInput = @strInput + '欢迎来到:台北面摊—章立民研究室';
END;
GO
CREATE PROCEDURE #您好
AS
BEGIN
DECLARE @strHi NVARCHAR(50);
SET @strHi = '嗨!您好!';
EXECUTE #欢迎 @strHi OUTPUT;
PRINT @strHi;
END
GO
--now to test whether the stored procedure "#您好" can be executed correctly
--we use three ways to test
PRINT '使用 EXECUTE 陈述式来测试';
EXECUTE #您好;
PRINT '';
GO
PRINT '使用 EXEC 陈述式来测试';
EXEC #您好;
PRINT '';
GO
PRINT '直接使用 Stored Procedure 名称来测试看看';
GO
#您好;
GO
Please note:
The stored procedures established by this example are temporary, and are automatically removed when you use an online and SQL Server instance that is interrupted.