[筆記]T-SQL output

  • 12798
  • 0
  • 2014-04-28

摘要:[筆記]T-SQL output

功能:

OUTPUT 子句,可以幫助你進行 INSERT、UPDATE、DELETE 或 MERGE 陳述式後,不用再透過SELECT,將被影響資料列資訊傳回

用法說明:

如果今天有個會員帳號資訊以及會員可使用的功能列表如下


CREATE TABLE #tempMember 
(
    MemberId INT IDENTITY(1,1) PRIMARY KEY, 
    MemberCode VARCHAR(12), 
    MemberStatus NVARCHAR(10),
    UpdatedUser VARCHAR(15),
    UpdatedTime DATETIME
) 

CREATE TABLE #tempFunction 
( 
    FunctionId INT IDENTITY(1,1) PRIMARY KEY, 
    FunctionName NVARCHAR(15), 
    MemberId INT, 
    FunctionEnable BIT 
) 

INSERT #tempMember 
    (MemberCode, MemberStatus) 
VALUES 
    ('MB2014041901', N'正常') 

DECLARE @MemberId INT 
SELECT @MemberId = IDENT_CURRENT ('#tempMember') 

INSERT #tempFunction 
    (MemberId, FunctionName, FunctionEnable) 
VALUES 
    (@MemberId, N'發文', 1), 
    (@MemberId, N'回覆留言', 1)

當我只能透過 MemberCode 將會員帳號設定為「暫時凍結」,且可用功能設定為「停用」的時候,我可以透過 OUTPUT 幫我帶回 MemberCode 所屬 MemberId,不需再SELECT取得資訊


DECLARE @UpdatedMember TABLE (MemberId INT) 

UPDATE #tempMember 
SET MemberStatus = N'暫時凍結' 
      , UpdatedUser = 'jgirl' 
      , UpdatedTime = GETDATE() 
OUTPUT INSERTED.MemberId INTO @UpdatedMember 
WHERE MemberCode = 'MB2014041901' 

UPDATE #tempFunction 
SET FunctionEnable = 0 
WHERE MemberId IN (SELECT MemberId FROM @UpdatedMember)

可以注意 SQL highlight 的地方,J妹使用了 table 變數,去接 OUTPUT 的結果,因為 OUTPUT 是將被影響的資料列資訊傳回,就算只異動了一筆資料,傳回一個欄位資訊,還是要使用 table 存放結果

更新完結果:

進階說明:

OUTPUT 可帶回兩種資訊

  • INSERTED.[column_name]:取回INSERT 、 UPDATE、MERGE完成後,被影響資料的資訊
  • DELETED.[column_name]:取回UPDATE、DELETE、MERGE完成前 ,被影響資料的資訊

所以也可以用在記錄 audit log 唷!


CREATE TABLE #tempMemberIo 
( 
    MemberId INT, 
    OriMemberStatus NVARCHAR(10), 
    NewMemberStatus NVARCHAR(10), 
    UpdatedUser VARCHAR(15), 
    UpdatedTime DATETIME 
) 

UPDATE #tempMember 
SET MemberStatus = N'正常' 
      , UpdatedUser = 'jgirl' 
      , UpdatedTime = GETDATE() 
OUTPUT INSERTED.MemberId 
      , DELETED.MemberStatus AS OriMemberStatus 
      , INSERTED.MemberStatus AS NewMemberStatus 
      , INSERTED.UpdatedUser AS UpdatedUser 
      , INSERTED.UpdatedTime AS UpdatedTime 
INTO #tempMemberIo 
WHERE MemberCode = 'MB2014041901' 

UPDATE #tempMember 
SET MemberStatus = N'永久凍結' 
      , UpdatedUser = 'jessica' 
      , UpdatedTime = GETDATE() 
OUTPUT INSERTED.MemberId 
      , DELETED.MemberStatus AS OriMemberStatus 
      , INSERTED.MemberStatus AS NewMemberStatus 
      , INSERTED.UpdatedUser AS UpdatedUser 
      , INSERTED.UpdatedTime AS UpdatedTime 
INTO #tempMemberIo 
WHERE MemberCode = 'MB2014041901' 

SELECT * FROM #tempMemberIo

 

參考資源: MSDN-OUTPUT 子句