[筆記]T-SQL output

2014-04-27

12799
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 子句

T-SQL

回首頁