如何将存储过程逐行分隔到表中

Question

有一个存储过程，其中包含许多插入、更新、删除和截断语句。我想将所有语句一一分组到一个表中。

例如：

create proc Get_Tables as
begin
UPDATE BB_FMCTransactionsTwo SET wsTradeDate = wsSettleDate WHERE wsEntryCode = 'NRT'
UPDATE BB_FMCTransactionsTwo SET wsOK = 1, wsSpecialLogic = SpecialLogic FROM BB_EntryCode INNER JOIN BB_FmcTransactionsTwo ON EntryCode = wsEntryCode
UPDATE BB_FMCTransactionsTwo SET wsFMCFtNt = '' FROM BB_EntryCode INNER JOIN BB_FmcTransactionsTwo ON EntryCode = wsEntryCode   WHERE wsBS <> 'B' AND ( FMCFtNtB IS NULL OR  FMCFtNtB = 'PR' )
UPDATE BB_B204_Tran SET AMOUNT = dbo.BCA_AMT(Desc1, Desc2, Desc3, Udesc1, Udesc2, Udesc3) WHERE TRANIND = 14 AND  PORTTYPE IN (2,3)
DELETE BB_B204_Tran WHERE TRANIND = 14 AND PORTTYPE IN(2,3) AND (Quantity IS NULL  OR ISNUMERIC(Quantity) = 0 OR Quantity = -1 OR ISNUMERIC(AMOUNT) = 0
end

必填：

我想将更新语句插入，将语句一一删除到表中...

创建一个存储过程，它

根据语句的类型（Insert、Update、Truncate）划分存储过程，并将其逐行插入到表中。因此，表格如下所示。

桌子：

Sl.No Statement
1 UPDATE BB_FMCTransactionsTwo SET wsTradeDate = wsSettleDate WHERE wsEntryCode = 'NRT'
2 DELETE BB_B204_Tran WHERE TRANIND = 14 AND PORTTYPE IN(2,3)

Answer 1

我已经为您的第一个更新语句实现了，并假设您的 AuditTable 将有一列 SQLData，因此您需要将这些 SQL 语句存储在存储过程中，如果您使用了任何变量，则需要存储到 SQL 数据变量中并插入到审计表中。 它可能会对您有所帮助。

declare @@SQL1 varchar(max)

UPDATE BB_FMCTransactionsTwo SET wsTradeDate = wsSettleDate WHERE wsEntryCode = 'NRT'
set @@SQL1 = 'UPDATE BB_FMCTransactionsTwo SET wsTradeDate = wsSettleDate WHERE wsEntryCode = ''NRT'''
insert into AuditTable (SQLData) values(@@SQL1)

对于变量：

DECLARE @@wsEntryCode VARCHAR(100)
SET @@wsEntryCode='NRT'
PRINT 'UPDATE BB_FMCTransactionsTwo SET wsTradeDate = wsSettleDate WHERE wsEntryCode = ''' + @@wsEntryCode + ''''
insert into AuditTable (SQLData) values(@@SQL1)