SQL Server stored procedure that returns the number of records processed

Question

I have a Winforms application that runs a stored procedure that parses multiple lines (about 500k). To inform the user about how many lines have already been processed, I will need a stored procedure that returns a value every n lines. For example, every 1000 rows are processed (most of them INSERT ).

Otherwise, I could only tell when ALL lines are processed. Any clues how to solve this?

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

Answer 1

So this is a very interesting question. I tried it about 5 years ago without success, so this is a small challenge for me :) Well, that’s what I have for you.

To send a message from SQL Server, you need to use the raiserror command with the nowait option. So I 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 will see that the message appears in the message section while the procedure is still running. Ok, we got messages from the server. Now we need to process it on the client.

For this, I created SQLCommand, like this

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

now catch the messages we use the InfoMessage of the SqlConnection object:

 cmd.Connection.InfoMessage += Connection_InfoMessage; static void Connection_InfoMessage(object sender, SqlInfoMessageEventArgs e) { Console.WriteLine(e.Message); } 

And now we are trying to display messages

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

SUCCESS:)

By the way, you cannot use ExecuteNonQuery() because it returns concatenated messages at the end of execution. In addition, you can run the request in the background so that it does not block your winform client.