SQL用户定义的函数与存储过程分支

I currently am working on a legacy application and have inherited some shady SQL with it. The project has never been put into production, but now is on it's way. During intial testing I found a bug. The application calls a stored procedure that calls many other stored procedures, creates cursors, loops through cursors, and many other things. FML.

我目前正在处理遗留应用程序,并继承了一些阴暗的SQL。该项目从未投入生产,但现在正在实施。在初始测试期间,我发现了一个错误。应用程序调用存储过程,该存储过程调用许多其他存储过程,创建游标,循环游标以及许多其他内容。 FML。

Currently the way the app is designed, it calls the stored procedure, then reloads the UI with a fresh set of data. Of course, the data we want to display is still being processed on the SQL server side, so the UI results are not complete when displayed. To fix this, I just made a thread sleep for 30 seconds, before loading the UI. This is a terrible hack and I would like to fix this properly on the SQL side of things.

目前,应用程序的设计方式是调用存储过程,然后使用一组新数据重新加载UI。当然,我们想要显示的数据仍在SQL服务器端处理,因此显示时UI结果不完整。为了解决这个问题,我在加载UI之前让线程休眠了30秒。这是一个可怕的黑客,我想在SQL方面正确解决这个问题。

My question is...is it worthwhile to convert the branching stored procedures to functions? Would this make the main-line stored procedure wait for a return value, before processing on?

我的问题是......将分支存储过程转换为函数是否值得?这会使主线存储过程在处理之前等待返回值吗?

Here is the stored procedure:

这是存储过程:

    ALTER PROCEDURE [dbo].[ALLOCATE_BUDGET] 
    @budget_scenario_id uniqueidentifier
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @constraint_type varchar(25)

    -- get project cache id and constraint type
    SELECT @constraint_type = CONSTRAINT_TYPE
    FROM BUDGET_SCENARIO WHERE BUDGET_SCENARIO_ID = @budget_scenario_id

    -- constraint type is Region by Region
    IF (@constraint_type = 'Region by Region')
      EXEC BUDGET_ALLOCATE_SCENARIO_REGIONBYREGION @budget_scenario_id

    -- constraint type is City Wide
    IF (@constraint_type = 'City Wide')
      EXEC BUDGET_ALLOCATE_SCENARIO_CITYWIDE @budget_scenario_id

    -- constraint type is Do Nothing
    IF (@constraint_type = 'Do Nothing')
      EXEC BUDGET_ALLOCATE_SCENARIO_DONOTHING @budget_scenario_id

    -- constraint type is Unconstrained
    IF (@constraint_type = 'Unconstrained')
      EXEC BUDGET_ALLOCATE_SCENARIO_UNCONSTRAINED @budget_scenario_id

    --set budget scenario status to "Allocated", so reporting tabs in the application are populated
    EXEC BUDGET_UPDATE_SCENARIO_STATUS @budget_scenario_id, 'Allocated'
END

To avoid displaying an incomplete resultset in the calling .NET application UI, before the cursors in the branching calls are completed, is it worthwile to convert these stored procedures into functions, with return values? Would this force SQL to wait before completing the main call to the [ALLOCATED_BUDGET] stored procedure?

为了避免在调用.NET应用程序UI中显示不完整的结果集,在分支调用中的游标完成之前,是否值得将这些存储过程转换为具有返回值的函数?这会强制SQL在完成对[ALLOCATED_BUDGET]存储过程的主调用之前等待吗?

The last SQL statement call in the stored procedure sets a status to "Allocated". This is happening before the cursors in the previous calls are finished processing. Does making these calls into function calls affect how the stored procedure returns focus to the application?

存储过程中的最后一个SQL语句调用将状态设置为“已分配”。这是在之前调用中的游标完成处理之前发生的。将这些调用转换为函数调用会影响存储过程如何将焦点返回给应用程序吗?

Any feedback is greatly appreciated. I have a feeling I am correct in going towards SQL functions but not 100% sure.

任何反馈都非常感谢。我有一种感觉,我在使用SQL函数时是正确的,但不是100%肯定。

** additional information:

** 附加信息:

Executing code uses [async=true] in the connection string

执行代码在连接字符串中使用[async = true]

Executing code uses the [SqlCommand].[ExecuteNonQuery] method

执行代码使用[SqlCommand]。[ExecuteNonQuery]方法

6 个解决方案

#1

How are you calling the procedure? I'm going to guess that you are using ExecuteNonQuery() to call the procedure. Try calling the procedure using ExecuteScalar() and modify the procedure like the following:

你是怎么称呼这个程序的?我猜你正在使用ExecuteNonQuery()来调用该过程。尝试使用ExecuteScalar()调用该过程并修改如下所示的过程:

ALTER PROCEDURE [dbo].[ALLOCATE_BUDGET] 
    @budget_scenario_id uniqueidentifier
AS
BEGIN
   ...

    RETURN True
END

This should cause your data execution code in .NET to wait for the procedure to complete before continuing. If you don't want your UI to "hang" during the procedure execution, use a BackgroundWorkerProcess or something similar to run the query on a separate thread and look for the completed callback to update the UI with the results.

这应该会导致.NET中的数据执行代码在继续之前等待该过程完成。如果您不希望UI在过程执行期间“挂起”,请使用BackgroundWorkerProcess或类似的东西在单独的线程上运行查询,并查找已完成的回调以使用结果更新UI。

#2

You could also try using the RETURN statement in your child stored procedures, which can be used to return a result code back to the parent procedure. You can call the child procedure by something along the lines of "exec @myresultcode = BUDGET_ALLOCATE_SCENARIO_REGIONBYREGION()". I think this should force the parent procedure to wait for the child procedure to finish.

您还可以尝试在子存储过程中使用RETURN语句,该语句可用于将结果代码返回给父过程。您可以通过“exec @myresultcode = BUDGET_ALLOCATE_SCARARIO_REGIONBYREGION()”的行来调用子过程。我认为这应该强制父进程等待子进程完成。

#3

I have never heard that it's possible for a stored procedure to return to the caller while still executing in the background.

我从来没有听说过,存储过程可能会在后台执行时返回给调用者。

In fact, I'll go as far as to say I don't believe that's happening. If you're seeing a difference between the UI and what you believe the SP should have done, then I believe it has a different cause.

事实上,我会说我不相信这种情况正在发生。如果您发现UI与您认为SP应该做的事情之间存在差异,那么我认为它有不同的原因。

Does the connection string have async=true in it? Is the SP being executed by using BeginExecuteReader or Begin-anything else?

连接字符串中是否包含async = true?是使用BeginExecuteReader还是Begin-anything执行SP?

#4

At the risk of sounding to simple, I suggest you could create a table which can store the status of the stored proc. Somehow, a flag that can indicate that the entire process & sub-process has finished executing.

冒着听起来很简单的风险,我建议你可以创建一个可以存储存储过程状态的表。不知何故,一个标志可以指示整个进程和子进程已经完成执行。

You could query this from UI to see if things are done by polling this status code.

您可以从UI查询此内容,以查看是否通过轮询此状态代码来完成任务。

#5

Does making these calls into function calls affect how the stored procedure returns focus to the application?

将这些调用转换为函数调用会影响存储过程如何将焦点返回给应用程序吗?

No.

The stored procedure has no idea that its caller is a UI application. There is nothing in the stored procedure that can influence the behavior of the UI application.

存储过程不知道其调用者是UI应用程序。存储过程中没有任何内容可以影响UI应用程序的行为。

Most likely the UI application is calling the stored procedure on one connection, and then refreshing its data on another connection. There's a plethora of ways of getting the UI to delay refreshing, but the one I'll push is that there should be a single database connection.

UI应用程序很可能是在一个连接上调用存储过程,然后在另一个连接上刷新其数据。有很多方法可以让UI延迟刷新,但我要推动的是应该有一个数据库连接。

#6

Personally, I would be far more concerned about replacing those cursors than converting this to functions.

就个人而言,我会更关心更换这些游标而不是将其转换为函数。

And I would not run the last proc until checking for a valid return code from the previous procs (this thing is in real trouble if one of the preceding procs dies!)

在检查前一个过程中的有效返回码之前我不会运行最后一个过程(如果前面的一个过程死了,这件事真的很麻烦!)

Also consider if this should all be in a transaction (are these procs changing data in a table?)

还要考虑这是否应该都在一个事务中(这些proc是否在表中更改数据?)

(Am I the only one who finds it funny you have a proc to run the process for Do Nothing?)

(我是唯一一个发现它很有趣的人,你有一个proc来运行什么都不做的过程?)

#1