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)

Mladen Prajdic · Accepted Answer · 2008-11-19 10:23:11Z

5

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

answered Nov 19, 2008 at 10:23

Mladen Prajdic

15.7k2 gold badges46 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nick DeVore · Accepted Answer · 2008-11-18 19:55:02Z

3

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

answered Nov 18, 2008 at 19:55

Nick DeVore

10.2k3 gold badges41 silver badges41 bronze badges

Comments

Marc Gravell · Accepted Answer · 2008-11-19 07:04:36Z

1

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

answered Nov 19, 2008 at 7:04

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related