Retrieving "output messages" from batch SQL command (SQL Server)

Question

I'm executing several discrete queries in a single batch against SQL Server. For example:

update tableX set colA = 'freedom';

select lastName from customers;

insert into tableY (a,b,c) values (x,y,z);

Now, I want to capture the result in a DataSet (from select statement) which is easy enough to do...but how do I also capture the "meta" response from that command similar to the way Query Analyzer/SQL Mgt Studio does when it displays the "Messages" tab and diplays something similar to:

(1 Row affected)
(2 Rows Updated)

Answer 1

look into SQL Connection events. I think that's what you're after: http://msdn.microsoft.com/en-us/library/a0hee08w.aspx

Answer 2

My suggestion would be to use the @@rowcount variable for this. Usually, when I'm doing these kind of commands, if I want to trap both the potential error and the rows affected, I do

declare @rowsAffected int, @error int

select * from sometable
     select @rowsAffected = @@rowcount, @error = @@error

if @@error <> 0 goto errorCleanup

Answer 3

Nick is right to suggest @@ROWCOUNT - in fact, as a matter of routine I always use SET NOCOUNT ON, which has a (small) performance benefit - but more importantly, this detail is an implementation detail - so you code shouldn't care...

If you want to return a value (such as number of rows updated), use any of:

• return value
• output parameter
• SELECT statement

The last has the most overhead