~楓花雪岳~: [SQL] Store Procedure

星期四, 9月 05, 2013

[SQL] Store Procedure - 參數傳遞

記錄 Store Procedure（以下簡稱 SP）的三種常用的參數傳遞方式

Sample Data

IF OBJECT_ID('Salary') IS NOT NULL
    DROP TABLE Salary

IF OBJECT_ID('Employ') IS NOT NULL
    DROP TABLE Employ

IF OBJECT_ID('uspInput') IS NOT NULL
    DROP PROCEDURE uspInput

IF OBJECT_ID('uspOutput') IS NOT NULL
    DROP PROCEDURE uspOutput

IF OBJECT_ID('uspReturn') IS NOT NULL
    DROP PROCEDURE uspReturn

CREATE TABLE Employ (EmpNO char(3) , EmpName nvarchar(8) , Leavedate date)
INSERT INTO Employ VALUES('001',N'張三','20130801')
INSERT INTO Employ VALUES('002',N'李四',NULL)
INSERT INTO Employ VALUES('003',N'王五',NULL)

CREATE TABLE Salary (EmpNO char(3),PayMonth datetime,bonus money,MonthSalary money)
INSERT INTO Salary VALUES('001','20130731',0,22000)
INSERT INTO Salary VALUES('002','20130630',1000,25000)
INSERT INTO Salary VALUES('002','20130731',3000,27000)
INSERT INTO Salary VALUES('003','20130531',5000,40000)
INSERT INTO Salary VALUES('003','20130630',7000,42000)
INSERT INTO Salary VALUES('003','20130630',8000,45000)

搭配 INPUT 參數使用

-- 建立 dbo.uspInput
CREATE PROCEDURE dbo.uspInput
(
    @EmpNO char(3) ,
    @Bonus money = 5000 -- 預設值
)
AS
  BEGIN 
      SELECT PayMonth , Bonus , MonthSalary
      FROM Salary
      WHERE EmpNO = @EmpNO 
          AND Bonus > @Bonus
  END
GO

-- 執行 1
EXEC dbo.uspInput @EmpNO = '003' , @Bonus = 1000
EXEC dbo.uspInput @Bonus = 1000 , @EmpNO = '003'
-- 執行 2
EXEC dbo.uspInput @EmpNO = '003' , 0
-- 執行 3
EXEC dbo.uspInput '003' , 7000
-- 執行 4
EXEC dbo.uspInput '003'

執行1：明確指定傳入參數名稱，不論傳入參數順序為何，都可以 run

執行2：一旦指定其中名稱一個參數名稱，全部參數就一定都要指定，執行 2 會出現下面的錯誤

訊息

執行3：皆不指定參數名稱，但傳進去的參數順序必須依造 SP 內的參數順序

執行4：SP 內參數有預設值，執行 SP 時，可以不傳入參數，會依 SP 內預設值（5000）

搭配 OUTPUT 參數使用

-- 建立 dbo.uspInput
CREATE PROCEDURE dbo.uspOutput
(
    @EmpNO char(3) ,
    @TotalBonus money OUTPUT -- OUTPUT 關鍵字
)
AS
  BEGIN 
      SELECT @TotalBonus = SUM(Bonus)
      FROM Salary
      WHERE EmpNO = @EmpNO
  END
GO 

-- 執行
DECLARE @TotalMoney AS money
EXEC dbo.uspOutput '003' , @TotalMoney OUTPUT -- OUTPUT 關鍵字
SELECT @TotalMoney AS [Output]

搭配 RETURN 參數使用

-- 建立 uspReturn
CREATE PROCEDURE dbo.uspReturn
(
    @EmpNO char(3) ,
    @Leavedate date OUTPUT
)
AS
  BEGIN 
  
      SELECT @Leavedate = Leavedate
      FROM Employ
      WHERE EmpNO = @EmpNO

      IF @@ROWCOUNT <> 0
          RETURN 1
      ELSE
          RETURN 0
  END
GO 

-- 執行
DECLARE @ExeResult AS bit , @Leavedate AS date
EXEC @ExeResult = dbo.uspReturn '001' , @Leavedate OUTPUT
IF @ExeResult = 1
    SELECT N'執行成功，該員工離職日為 ' + CONVERT(char(10) , @Leavedate , 120)
ELSE
    SELECT N'執行失敗'