如何在sql中逐行将存储过程分成表？ [英] How to segregate a stored procedure into a table row by row in sql?

问题描述

有一个存储过程，其中包含许多insert，update，delete和truncate语句。我想将所有语句逐个分组到表中...



例如

 创建  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 
 更新 BB_FMCTransactionsTwo  SET  wsFMCFtNt = ' '  FROM  BB_EntryCode < span class =code-keyword> INNER   JOIN  BB_FmcTransactionsTwo  ON  EntryCode = wsEntryCode  WHERE  wsBS<> '  B'  AND （FMCFtNtB  IS   NULL  或 FMCFtNtB = '  PR'）
 更新 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 （数量 IS   NULL   OR  ISNUMERIC（Quantity）=  0   OR 数量= -1 或 ISNUMERIC（AMOUNT）=  0  
 
 结束 

----必需---

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

创建一个SP，根据statemnts的类型划分SP（Insert，Update ，Truncate）并将其逐行插入表中。所以表格如下所示







表



Sl.No声明

1更新BB_FMCTransactionsTwo SET wsTradeDate = wsSettleDate WHERE wsEntryCode ='NRT'

2 DELETE BB_B204_Tran WHERE TRANIND = 14 AND PORTTYPE IN（2,3）

解决方案

无法猜测为什么要这样做，但我看不出问题：存储过程源只是纯文本data：将它的每一行存储在表格的一行。



[update]

如果你必须区分不同的语句（并处理不同的存储过程），然后你必须解析存储过程本身。一般来说，这是一项公平复杂的任务，但如果您能够将约束放入输入存储过程，则可以简化很多任务。

[/ update]

There is a stored procedure which contains many insert ,update , delete and truncate statements.I want to group all the statements one by one into a table...

For example

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

----Required---
I want to insert the update statements,delete statements into a table one by one...
Create an SP which divides the SP based on type of statemnts(Insert,Update,Truncate) and inserts that into a table row by row..so that the table looks like below



Table

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)

解决方案

Cannot guess why you want to do that, but I can't see problems: the stored procedure source is just plain textual data: store each line of it in a row of the table.

[update]
If you have to distinguish the different statements (and deal with different stored procedures) then you must parse the stored procedure themselves. This is, generally speaking, a fair complex task, but you may simplify a lot it if you are able to put constraints to the input stored procedures.
[/update]

这篇关于如何在sql中逐行将存储过程分成表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！