SQL Server stored procedure that returns processed records number

Question

I have a Winforms application that executes a stored procedure which examines several rows (around 500k). In order to inform the user about how many rows have already been processed, I would need a stored procedure which returns a value every n rows. For example, every 1000 rows processed (most are INSERT).

Otherwise I would be able only to inform when ALL rows are processed. Any hints how to solve this?

I thought it could be useful to use a trigger or some scheduled task, but I cannot figure out how to implement it.

Answer 1

So this is very interesting question. I've tried it about 5 years ago with no success, so this is a little challenge for me :) Well, here's is what I've got for you.

To send message from SQL Server you need to use raiserror command with nowait option. So I've wrote a stored procedure

create procedure sp_test
as
begin
    declare @i bigint, @m nvarchar(max)

     select @i = 1

     while @i < 10
     begin
         waitfor delay '00:00:01'
         select @m = cast(@i as nvarchar(max))
         raiserror(@m, 0, 0) with nowait
         select @i = @i + 1
     end
end

If you try to execute it in SSMS, you'll see that message appearing in message section while procedure still works. Ok, we got messages from server. Now we need to process it on the client.

To do that, I've created a SQLCommand like this

SqlCommand cmd = new SqlCommand("sp_Test");
cmd.Connection = new SqlConnection("Server=HOME;Database=Test;Trusted_Connection=True;");

now to catch a messages we using InfoMessage of SqlConnection object:

cmd.Connection.InfoMessage += Connection_InfoMessage;

static void Connection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
    Console.WriteLine(e.Message);
}

And now we're trying to display messages

cmd.Connection.Open();
try
{
    SqlDataReader r = cmd.ExecuteReader();
}
finally
{
    cmd.Connection.Close();
}

SUCCESS :)

BTW, you cannot use ExecuteNonQuery(), because it returns concatenated messages at the end of execution. Also, you may want to run your query in background mode, so it will not lock you winform client.