Author |
Topic |
wided
Posting Yak Master
218 Posts |
Posted - 2013-02-20 : 09:02:34
|
Following a request insert, I would like to retrieve the number of rows insertedthank you |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-20 : 09:09:48
|
INSERT Statement@@ROWCOUNT will give number of rows affected by recent DML--Chandu |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-20 : 09:10:52
|
You can use the @@ROWCOUNT system function. Couple of things to keep in mind about using it are:a) If you need to use it later, capture it immediately after the insert statement and store it in a variable.DECLARE @rows_inserted INT;-- your insert statement hereSET @rows_inserted = @@ROWCOUNT; b) If you are using MERGE, @@ROWCOUNT would reflect the number of rows affected (including updates, deletes0 rather than just the number of rows inserted. |
|
|
wided
Posting Yak Master
218 Posts |
Posted - 2013-02-20 : 09:23:11
|
MY QUERY IS THIS:INSERT INTO U41_TachesWorkFlowDetail select t1.WorkFlowID,t3.EmployeID , @wEtape from Inserted t1, h11_employe t2 , U41_UserValidateWF t3 where t1.employeId= t2.EmployeID and t2.ChefHierarchiqueId= t3.EmployeID and profil = @wEtapePRINT @@ROWCOUNTis returns 0 (why), at least one line is insertedreturn 0 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-20 : 09:41:01
|
Assuming you do not have any other statements between the insert and the print, it should print out the number of rows inserted. If it is printing out 0, no rows were inserted. How were you verifying that at least one row was inserted? |
|
|
wided
Posting Yak Master
218 Posts |
Posted - 2013-02-20 : 09:57:11
|
I checked the table directly in the database |
|
|
wided
Posting Yak Master
218 Posts |
Posted - 2013-02-20 : 10:18:59
|
it insert this in my table(U41_TachesWorkFlowDetail)26 28 35035 CH27 28 35035 DRHI need to know if inserted a row to stop processingjust26 28 35035 CHafter insert, i do this:if @@ROWCOUNT > 0 breakI would like to exit the loop when inserting a row in this tableAre there any other means |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-20 : 11:12:14
|
That should work. How is your logic set up? does your while loop have a begin and end? See this example - it should run through the loop only once.WHILE (1=1)BEGIN SELECT '1'; IF (@@ROWCOUNT > 0) BREAK;END |
|
|
wided
Posting Yak Master
218 Posts |
Posted - 2013-02-21 : 04:16:16
|
Hi JamesWHILE @Status=0BEGIN FETCH next FROM curs INTO @num, @wEtape SELECT @Status = @@fetch_status IF @Status <> 0 BREAK --status curs IF @flag <> 0 BREAK -- if @wEtape is not null AND @flag =0 begin INSERT INTO U41_TachesWorkFlowDetail select t1.WorkFlowID,t3.EmployeID , @wEtape from Inserted t1, h11_employe t2 , U41_UserValidateWF t3 where t1.employeId= t2.EmployeID and t2.ChefHierarchiqueId= t3.EmployeID and profil = @wEtape end if @@rowcount >0 select @flag = 1 end --whileThis is my queryI added one which is flag = 1 if @@ rowcount> 0 (thus recording added)Then I added:IF @ flag <> 0 BREAKI put "print" in my procedurethe result:wEtape = CHflag =1wEtape = DRHflag = 1normally the procedure should stop the insersion the "CH"but the system inserts CH and DRH |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-21 : 04:30:02
|
Where are you assigning flag value at first time?--Chandu |
|
|
|