主页 / dba / 问题 / 132673
Accepted
Bogdan Bogdanov
Asked: 2016-03-19 09:24:00 +0800 CST

如何在存储过程完成之前获得响应？

  • 772

我需要在存储过程完成之前返回部分结果（作为简单的选择）。

有可能这样做吗？

如果是，该怎么做？

如果没有，有什么解决方法吗？

编辑：我有几个部分的程序。在第一部分中，我计算了几个字符串。我稍后会在过程中使用它们来进行额外的操作。问题是调用者尽快需要字符串。所以我需要计算该字符串并将其传回（以某种方式，例如从选择中），然后继续工作。调用者更快地获得其有价值的字符串。

调用者是一个 Web 服务。

t-sql sql-server-2012

4 个回答

  1. Best Answer

    您可能正在寻找RAISERROR带有该NOWAIT选项的命令。

    根据评论：

    RAISERROR 可用作 PRINT 的替代方法，将消息返回给调用应用程序。

    这不会从SELECT语句返回结果，但它会让您将消息/字符串传递回客户端。如果您想返回您选择的数据的快速子集，那么您可能需要考虑FAST查询提示。

    指定查询已针对第一个 number_rows 的快速检索进行了优化。这是一个非负整数。返回第一个 number_rows 后，查询继续执行并生成完整的结果集。

    Shannon Severance在评论中添加：

    来自Erland Sommarskog的 SQL Server 中的错误和事务处理：

    但请注意，某些 API 和工具可能会在其一侧缓冲，从而抵消WITH NOWAIT.

    有关完整上下文，请参阅源文章。

    • 11

  2. OP 已经尝试发送多个结果集（不是 MARS），并且已经看到它确实在返回任何结果集之前等待存储过程完成。考虑到这种情况，这里有一些选择：

    1. 如果您的数据足够小以适合 128 字节，您很可能使用SET CONTEXT_INFOwhich 应该使该值通过SELECT [context_info] FROM [sys].[dm_exec_requests] WHERE [session_id] = @SessionID;. 您只需要在运行存储过程之前执行一个快速查询SELECT @@SPID;并通过SqlCommand.ExecuteScalar.

      我刚刚对此进行了测试，它确实有效。

    2. 类似于@David 将数据放入“进度”表的建议，但无需处理清理或并发/进程分离问题：

      1. 在应用程序代码中创建一个新Guid的并将其作为参数传递给存储过程。将此 Guid 存储在一个变量中，因为它将被多次使用。
      2. 在存储过程中，使用该 Guid 作为表名的一部分创建一个全局临时表，类似于CREATE TABLE ##MyProcess_{GuidFromApp};. 该表可以包含您需要的任何数据类型的任何列。

      3. 每当您拥有数据时，将其插入到该全局临时表中。

      4. 在应用程序代码中，开始尝试读取数据，但将其包装SELECT在 an 中，IF EXISTS以便在尚未创建表的情况下不会失败：

        IF (OBJECT_ID('tempdb..[##MyProcess_{0}]')
    IS NOT NULL)
BEGIN
  SELECT * FROM [##MyProcess_{0}];
END;

      使用String.Format()，您可以替换{0}为 Guid 变量中的值。测试 if Reader.HasRows，如果为 true 则读取结果，否则调用Thread.Sleep()或其他任何东西然后再次轮询。

      好处：

      • 该表与其他进程隔离，因为只有应用程序代码知道特定的 Guid 值，因此无需担心其他进程。另一个进程将拥有自己的私有全局临时表。
      • 因为它是一个表，所以一切都是强类型的。
      • 因为它是一个临时表，当执行存储过程的会话结束时，该表将被自动清理。
      • 因为是全局临时表：
        • 其他会话可以访问它，就像永久表一样
        • 它将在创建它的子进程结束后继续存在（即EXEC/sp_executesql调用）


      我已经对此进行了测试，并且可以按预期工作。您可以使用以下示例代码自己尝试。

      在一个查询选项卡中，运行以下命令，然后突出显示块注释中的 3 行并运行：

      CREATE
--ALTER
PROCEDURE #GetSomeInfoBackQuickly
(
  @MessageTableName NVARCHAR(50) -- might not always be a GUID
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX) = N'CREATE TABLE [##MyProcess_' + @MessageTableName
             + N'] (Message1 NVARCHAR(50), Message2 NVARCHAR(50), SomeNumber INT);';

-- Do some calculations

EXEC (@SQL);

SET @SQL = N'INSERT INTO [##MyProcess_' + @MessageTableName
+ N'] (Message1, Message2, SomeNumber) VALUES (@Msg1, @Msg2, @SomeNum);';

DECLARE @SomeNumber INT = CRYPT_GEN_RANDOM(2);

EXEC sp_executesql
    @SQL,
    N'@Msg1 NVARCHAR(50), @Msg2 NVARCHAR(50), @SomeNum INT',
    @Msg1 = N'wow',
    @Msg2 = N'yadda yadda yadda',
    @SomeNum = @SomeNumber;

WAITFOR DELAY '00:00:10.000';

SET @SomeNumber = CRYPT_GEN_RANDOM(3);
EXEC sp_executesql
    @SQL,
    N'@Msg1 NVARCHAR(50), @Msg2 NVARCHAR(50), @SomeNum INT',
    @Msg1 = N'wow',
    @Msg2 = N'yadda yadda yadda',
    @SomeNum = @SomeNumber;

WAITFOR DELAY '00:00:10.000';
GO
/*
DECLARE @TempTableID NVARCHAR(50) = NEWID();
RAISERROR('%s', 10, 1, @TempTableID) WITH NOWAIT;

EXEC #GetSomeInfoBackQuickly @TempTableID;
*/

      转到“消息”选项卡并复制打印的 GUID。然后，打开另一个查询选项卡并运行以下命令，将您从另一个会话的消息选项卡复制的 GUID 放入第 1 行的变量初始化中：

      DECLARE @TempTableID NVARCHAR(50) = N'GUID-from-other-session';

EXEC (N'SELECT * FROM [##MyProcess_' + @TempTableID + N']');

      继续打F5。您应该在前 10 秒看到 1 个条目，然后在接下来的 10 秒看到 2 个条目。

    3. 您可以使用 SQLCLR 通过 Web 服务或其他方式调用您的应用程序。

    4. 您也许可以使用PRINT/RAISERROR(..., 1, 10) WITH NOWAIT立即将字符串传回，但由于以下问题，这会有点棘手：

      • “消息”输出仅限于VARCHAR(8000)或NVARCHAR(4000)
      • 消息的发送方式与结果不同。为了捕获它们，您需要设置一个事件处理程序。在这种情况下，您可以创建一个变量作为静态集合，以获取可用于所有代码部分的消息。或者也许是其他方式。我在这里的其他答案中有一个或两个示例，展示了如何捕获消息，并在以后找到它们时链接到它们。

      • 默认情况下，在该过程完成之前也不会发送消息。但是，可以通过将SqlConnection.FireInfoMessageEventOnUserErrors 属性设置为来更改此行为true。该文档指出：

        当您将 FireInfoMessageEventOnUserErrors 设置为true时，以前被视为异常的错误现在将作为 InfoMessage 事件处理。所有事件立即触发并由事件处理程序处理。如果将 FireInfoMessageEventOnUserErrors 设置为false，则在过程结束时处理 InfoMessage 事件。

        这里的缺点是大多数 SQL 错误将不再引发SqlException. 在这种情况下，您需要测试传递给消息事件处理程序的其他事件属性。这适用于整个连接，这使事情变得有点棘手，但并非难以管理。

      • 所有消息都出现在同一级别，没有单独的字段或属性来区分它们。接收它们的顺序应该与发送它们的顺序相同，但不确定这是否足够可靠。您可能需要包含一个标签或其他可以解析的东西。这样你至少可以确定哪个是哪个。

    • 5

  3. 更新： 请参阅 strutzky 的回答（上面）和至少一个示例的评论，其中它的行为与我预期的不同，并在此处描述。在时间允许的情况下，我将不得不进一步试验/阅读以更新我的理解......

    如果您的调用者与数据库异步交互或线程/多进程，因此您可以在第一个会话仍在运行时打开第二个会话，您可以创建一个表来保存部分数据并随着过程的进行更新它。然后可以通过设置了事务隔离级别1的第二个会话来读取它，以使其能够读取未提交的更改：

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM progress_table

    1：根据 srutzky 的回答中的评论和后续更新，如果被监控的进程没有包含在事务中，则不需要设置隔离级别，尽管我倾向于在这种情况下将其设置为习惯，因为它不会导致在这些情况下不需要时造成伤害

    当然，如果您可以有多个进程以这种方式运行（如果您的 Web 服务器接受并发用户，并且这种情况很少见），您将需要以某种方式识别此进程的进度信息. 或许将新生成的 UUID 作为键传递给该过程，将其添加到进度表中，然后读取：

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM progress_table WHERE process = <current_process_uuid>

    我已经使用这种方法来监控 SSMS 中长时间运行的手动进程。我无法决定它是否“闻起来”太多让我考虑在生产中使用它......

    • 5

  4. 如果您的存储过程需要在后台运行（即异步），那么您应该使用 Service Broker。设置起来有点麻烦，但是一旦完成，您将能够启动存储过程（非阻塞）并尽可能长时间（或尽可能少地）收听进度消息。

    • 0

相关问题