August 5, 2009 at 12:44 pm
Hi,
I have about 5 inserts and 4 updates in a stored procedure. I need to store the counts of each individual insert/update statement and pass them onto another stored procedure. The way I am thinking of doing is below:
Update 1 Statement...
SET @value = @@rowcount
EXEC dbo.SPCount 1, 'Update1', @value
--Above update, first parameter 1 is the application id It's irrelevant so you can ignore that, second parameter 'Update1' is the description and the third parameter is the @@rowcount value stored for the updated items.
Update 2 Statement...
SET @value = @@rowcount
EXEC dbo.SPCount 1, 'Update2', @value
INSERT 1 Statement...
SET @value = @@rowcount
EXEC dbo.SPCount 1, 'INSERT1', @value
And so on... now.. I'm wondering, is this going to work as I want it to meaning Is the variable @value getting reset after each update insert and recording the @@rowcount for each statement after it is set from scratch and then passing it to the dbo.SPCount procedure?
As a seperate question...can I use where clause in @@rowcount.. like so:
SET @value = ( select @@rowcount from UPDATEDtable where status = 'ABC' )
Will appreciate the help. Thank you.
S
--
:hehe:
August 5, 2009 at 1:00 pm
You can use @@Rowcount and your @value variable that way.
If you use @@Rowcount with a Where clause like you have, the code will compile and run, but it won't give you what you need. It'll just return the whole number of @@Rowcount.
If you need to know how many rows were updated with some specific criteria, your best bet is to output the updated data into a table variable, then select a count of rows from that with the criteria you need. Take a look at "Output" in Books Online. It's one of the new features for 2005/2008 (can I still call feature "new" if it's in 2005?), and it's very, very useful. Allows you to return data from your update/delete/insert statement and what rows were affected. Works very similarly to the "inserted" and "deleted" tables in triggers, if you're familiar with those.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 6, 2009 at 8:25 am
GSquared,
In another post, I saw Lynn say that the variable needs to be initiliazed to zero (0).
My concern is, does the variable have to be initialized after every update/insert? meaning SET @value = 0.0
EDIT: Problem solved, no initialization.
--
:hehe:
August 7, 2009 at 9:20 am
"Initialization" only happens once by definition. You will always get a return from @@rowcount, so the variable will always reflect the most recent value.
August 7, 2009 at 9:37 am
Got it. Thanks for the confirmation :w00t:
--
:hehe:
August 7, 2009 at 11:01 am
Sounds like you've got a handle on it now. Cool biz.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply