摘要:[筆記]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 子句