I have a few stored procedures that return the same set of data (same columns) to a user. The stored procedure called depends on certain conditions. These stored procedures are fairly intensive and are being run by every user of the system. I would like to create stored procedure that calls each of these procedures and stores the data on a separate table. I will then run this new stored procedure every 5 minutes or so and let the users pull from the new table.
我有一些存储过程将相同的数据集(相同的列)返回给用户。调用的存储过程取决于某些条件。这些存储过程相当密集,并且由系统的每个用户运行。我想创建调用每个过程的存储过程并将数据存储在单独的表中。然后,我将每隔5分钟左右运行一次这个新的存储过程,让用户从新表中拉出来。
T_OutboundCallList is a permanent table with the same columns as returned by the two stored procedures.
T_OutboundCallList是一个永久表,其列与两个存储过程返回的列相同。
I would like something like the following but when I try to run this it just runs continuously and I have to stop the procedure.
我想要像下面这样的东西,但当我尝试运行它时,它只是连续运行,我必须停止程序。
BEGIN
TRUNCATE TABLE T_OutboundCallList
INSERT T_OutboundCallList EXECUTE p_LeadVendor_GetCallsForCallList
INSERT T_OutboundCallList EXECUTE p_CallLog_GetAbandonedCallsCallList
END
Each of the procedures (*CallList) return a list of calls to be made and I do want them entered into the new table in this order (LeadVendor calls before AbandonedCalls). I also need to clear the table before adding the calls as there may be new calls that need to be higher in the list.
每个过程(* CallList)返回一个要进行的调用列表,我确实希望它们按此顺序进入新表(LeadVendor在AbandonedCalls之前调用)。我还需要在添加调用之前清除表,因为列表中可能需要更高的新调用。
Is there some problem with this procedure that I am not seeing?
我没有看到这个程序有什么问题吗?
Thanks, Brian
1 个解决方案
#1
2
Without seeing the code in your *CallList procs it is hard to say what issue you are having. You should have the insert commands inside of your nested procedure. You can use the results of a procedure to insert data, but not like you are above. It is using OPENROWSET, and I think you will be better off the way I suggested.
如果没有看到你的* CallList过程中的代码,很难说你遇到了什么问题。您应该在嵌套过程中使用insert命令。您可以使用过程的结果来插入数据,但不能像上面那样。它正在使用OPENROWSET,我认为你会比我建议的方式更好。
#1
2
Without seeing the code in your *CallList procs it is hard to say what issue you are having. You should have the insert commands inside of your nested procedure. You can use the results of a procedure to insert data, but not like you are above. It is using OPENROWSET, and I think you will be better off the way I suggested.
如果没有看到你的* CallList过程中的代码,很难说你遇到了什么问题。您应该在嵌套过程中使用insert命令。您可以使用过程的结果来插入数据,但不能像上面那样。它正在使用OPENROWSET,我认为你会比我建议的方式更好。