We help IT Professionals succeed at work.

Get number or records deleted by stored proc

vbhargav80
vbhargav80 asked
on
308 Views
Last Modified: 2010-04-21
Dear Experts,

I have a stored procedure that issues multiple delete statements for different tables. I want to execute it using c# code, but how do I get the number records returned by each delete statement in the stored proc?

Using ExecuteNonQuery() of the command object will only give me the number of records returned by the last delete statement.
Comment
Watch Question

Jaime OlivaresSoftware Architect
CERTIFIED EXPERT
Top Expert 2008

Commented:
You need a query like this:

sqlCom = new SqlCommand("DELETE FROM XXXX WHERE YYYY; SELECT @@ROWCOUNT;", .....);

Then use:

int count = (int)sqlCom.ExecuteScalar();

Author

Commented:
Hi Jaime,

sorry if my question does not sound right. What i meant is that I have a stored proc which does something like

delete from tableA
delete from tableB
delete from tableC

When I execute my stored proc from C# I want to know how many records were deleted from tableA, tableB and tableC.

Thanks
Jaime OlivaresSoftware Architect
CERTIFIED EXPERT
Top Expert 2008

Commented:
you have to return the value from your SP specifically.
So, inside your SP, create a variable, let's say @count
the, after each delete statement do:

count integer := 0
delete from tableA
count := count + @@ROWCOUNT
delete from tableB
count := count + @@ROWCOUNT
delete from tableC
count := count + @@ROWCOUNT

return the count value at the end of SP

Finally, call the stored procedure with ExecuteScalar as suggested to trap the count result

Author

Commented:
But how can I know the individual counts for tableA, tableB and tableC instead of the total count.

Jaime OlivaresSoftware Architect
CERTIFIED EXPERT
Top Expert 2008

Commented:
you need to pass variables for each counter, like:


CREATE Procedure P_DeleteRecords
(@ID varchar(10) = NULL,   <----------- Some useful input data
 @count1 int = NULL OUTPUT,
 @count2 int = NULL OUTPUT,
 @count3 int = NULL OUTPUT)
as
begin
delete from tableA
@count1 := @@ROWCOUNT
delete from tableB
@count2 := @@ROWCOUNT
delete from tableC
@count3 := @@ROWCOUNT
end

Open in new window

Software Architect
CERTIFIED EXPERT
Top Expert 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Sorry, I went on a holiday so took some time to close this off.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.