存儲過程中調用存儲過程

use Northwind

GO

IF EXISTS(SELECT name FROM sysobjects

      WHERE name = 'p_getEmpleeIdByName' AND type = 'P')

   DROP PROCEDURE p_getEmpleeIdByName

GO

create proc p_getEmpleeIdByName

 @inputEmpFirstName varchar(50),

 @outId int out

as

 select @outId = employeeid from employees where FirstName [email protected]

IF EXISTS(SELECT name FROM sysobjects

      WHERE name = 'p_getEmployeeInfo' AND type = 'P')

   DROP PROCEDURE p_getEmployeeInfo

GO

create proc p_getEmployeeInfo

 @inputEmpFirstName varchar(50)

as

  declare @@myId int

  exec p_getEmpleeIdByName @inputEmpFirstName,@@myId out  -- 調用 -- 調用 p_getEmpleeIdByName 存儲過程 間接拿到EmployeeId存儲過程 間接拿到EmployeeId

 select * from employees where employeeId = @@myId

GO

---- 應用

p_getEmployeeInfo 'Andrew'

GO

----注意 out 變量