Como obter resposta do procedimento armazenado antes de terminar?

Você provavelmente está procurando o RAISERRORcomando com a NOWAITopção.

Pelas observações :

RAISERROR pode ser usado como uma alternativa para PRINT para retornar mensagens para aplicativos de chamada.

Isso não retorna os resultados de uma SELECTinstrução, mas permite que você passe mensagens/strings de volta para o cliente. Se você deseja retornar um subconjunto rápido dos dados que está selecionando, considere a FASTdica de consulta.

Especifica que a consulta é otimizada para recuperação rápida das primeiras number_rows. Este é um número inteiro não negativo. Depois que os primeiros number_rows são retornados, a consulta continua a execução e produz seu conjunto de resultados completo.

Adicionado por Shannon Severance em um comentário:

De Error and Transaction Handling in SQL Server por Erland Sommarskog:

Cuidado, porém, que algumas APIs e ferramentas podem armazenar em buffer, anulando assim o efeito do WITH NOWAIT.

Veja o artigo de origem para o contexto completo.

O OP já tentou enviar vários conjuntos de resultados (não MARS) e viu que realmente espera a conclusão do procedimento armazenado antes de retornar qualquer conjunto de resultados. Com essa situação em mente, aqui estão algumas opções:

1. Se seus dados forem pequenos o suficiente para caber em 128 bytes, você provavelmente poderá usar SET CONTEXT_INFOo que deve tornar esse valor visível por meio de SELECT [context_info] FROM [sys].[dm_exec_requests] WHERE [session_id] = @SessionID;. Você só precisaria executar uma consulta rápida antes de executar o Stored Procedure para SELECT @@SPID;e pegá-lo via SqlCommand.ExecuteScalar.

   Acabei de testar isso e funciona.

2. Semelhante à sugestão de @David de colocar os dados em uma tabela de "progresso", mas sem precisar mexer com problemas de limpeza ou simultaneidade / separação de processos:

   1. Crie um novo Guiddentro do código do aplicativo e passe-o como um parâmetro para o procedimento armazenado. Armazene este Guid em uma variável, pois ele será usado várias vezes.
   2. No procedimento armazenado, crie uma tabela temporária global usando esse Guid como parte do nome da tabela, algo como CREATE TABLE ##MyProcess_{GuidFromApp};. A tabela pode ter quaisquer colunas de quaisquer tipos de dados que você precisar.

   3. Sempre que você tiver os dados, insira-os nessa Tabela Temp Global.

   4. No código do aplicativo, comece a tentar ler os dados, mas envolva-os SELECTem um IF EXISTSpara que não haja falha se a tabela ainda não tiver sido criada:

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

   Com String.Format(), você pode substituir {0}pelo valor na variável Guid. Teste para if Reader.HasRows, e se for true, leia os resultados, caso contrário, ligue Thread.Sleep()ou o que quer que seja para pesquisar novamente.

   Benefícios:

   • Essa tabela é isolada de outros processos, pois apenas o código do aplicativo conhece o valor Guid específico, portanto, não há necessidade de se preocupar com outros processos. Outro processo terá sua própria tabela temporária global privada.
   • Por se tratar de uma tabela, tudo é fortemente tipado.
   • Por ser uma tabela temporária, quando a sessão que executa o Stored Procedure terminar, a tabela será limpa automaticamente.
   • Porque é uma tabela temporária global :
     • é acessível por outras sessões, assim como uma tabela permanente
     • ele sobreviverá ao término do subprocesso no qual foi criado (ou seja, a chamada EXEC/ sp_executesql)

   
   Eu testei isso e funciona como esperado. Você pode tentar por si mesmo com o código de exemplo a seguir.

   Em uma guia de consulta, execute o seguinte e, em seguida, destaque as 3 linhas no comentário de bloco e execute-o:

   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;
   */
   

   Vá para a guia "Mensagens" e copie o GUID que foi impresso. Em seguida, abra outra guia de consulta e execute o seguinte, colocando o GUID que você copiou da guia Mensagens da outra sessão na inicialização da variável na linha 1:

   DECLARE @TempTableID NVARCHAR(50) = N'GUID-from-other-session';
   
   EXEC (N'SELECT * FROM [##MyProcess_' + @TempTableID + N']');
   

   Continue batendo F5. Você deve ver 1 entrada nos primeiros 10 segundos e, em seguida, 2 entradas nos próximos 10 segundos.

3. Você pode usar o SQLCLR para fazer uma chamada de volta para seu aplicativo por meio de um serviço da Web ou de algum outro meio.

4. Talvez você possa usar PRINT/ RAISERROR(..., 1, 10) WITH NOWAITpara passar strings de volta imediatamente, mas isso seria um pouco complicado devido aos seguintes problemas:

   • A saída "Mensagem" é restrita a VARCHAR(8000)ouNVARCHAR(4000)
   • As mensagens não são enviadas da mesma maneira que os resultados. Para capturá-los, você precisa configurar um manipulador de eventos. Nesse caso, você poderia criar uma variável como uma coleção estática para obter as mensagens que estariam disponíveis para todas as partes do código. Ou talvez de outra forma. Eu tenho um exemplo ou dois em outras respostas aqui mostrando como capturar as mensagens e vou vinculá-las mais tarde quando as encontrar.

   • As mensagens, por padrão, também não são enviadas até que o processo seja concluído. Esse comportamento, no entanto, pode ser alterado definindo a propriedade SqlConnection.FireInfoMessageEventOnUserErrors como true. A documentação afirma:

     Quando você define FireInfoMessageEventOnUserErrors como true , os erros que antes eram tratados como exceções agora são tratados como eventos InfoMessage. Todos os eventos disparam imediatamente e são manipulados pelo manipulador de eventos. Se FireInfoMessageEventOnUserErrors for definido como false, os eventos InfoMessage serão tratados no final do procedimento.

     A desvantagem aqui é que a maioria dos erros de SQL não gerará mais um arquivo SqlException. Nesse caso, você precisa testar propriedades de eventos adicionais que são passadas para o Manipulador de Eventos de Mensagem. Isso vale para toda a conexão, o que torna as coisas um pouco mais complicadas, mas não incontroláveis.

   • Todas as mensagens aparecem no mesmo nível sem nenhum campo ou propriedade separada para distinguir uma da outra. A ordem em que são recebidos deve ser a mesma de como são enviados, mas não tenho certeza se isso é confiável o suficiente. Pode ser necessário incluir uma tag ou algo que possa ser analisado. Dessa forma, você poderia pelo menos ter certeza de qual é qual.

ATUALIZAÇÃO: Veja a resposta de strutzky ( acima ) e os comentários para pelo menos um exemplo em que isso não se comporta como eu espero e descrevo aqui. Terei que experimentar/ler mais para atualizar meu entendimento quando o tempo permitir...

Se o chamador interage com o banco de dados de forma assíncrona ou é encadeado/multiprocesso, para que você possa abrir uma segunda sessão enquanto a primeira ainda está em execução, você pode criar uma tabela para manter os dados parciais e atualizá-los à medida que o procedimento avança. Isso pode ser lido por uma segunda sessão com o nível de isolamento de transação ¹ definido para permitir a leitura de alterações não confirmadas:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM progress_table

^{1: de acordo com os comentários e a atualização subsequente na resposta de srutzky, não é necessário definir o nível de isolamento se o processo que está sendo monitorado não estiver envolvido em uma transação, embora eu tenha a tendência de defini-lo como hábito em tais circunstâncias, pois não causa prejudicar quando não necessário nesses casos}

Obviamente, se você puder ter vários processos operando dessa maneira (o que é provável se o seu servidor da Web aceitar usuários simultâneos e é muito raro que não seja o caso), você precisará identificar as informações de progresso desse processo de alguma forma . Talvez passe para o procedimento um UUID recém-criado como uma chave, adicione-o à tabela de progresso e leia com:

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

Eu usei esse método para monitorar processos manuais de longa duração no SSMS. Não consigo decidir se "cheira" demais para eu considerar usá-lo na produção...

Se o procedimento armazenado precisar ser executado em segundo plano (ou seja, de forma assíncrona), você deverá usar o Service Broker. É um pouco trabalhoso de configurar, mas uma vez feito, você poderá iniciar o procedimento armazenado (sem bloqueio) e ouvir as mensagens de progresso pelo tempo (ou pouco) que desejar.