| Author |
 Topic  |
|
|
wided
Posting Yak Master
152 Posts |
 Posted - 02/20/2013 : 09:02:34
|
Following a request insert, I would like to retrieve the number of rows inserted
thank you |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1391 Posts |
Posted - 02/20/2013 : 09:09:48
|
INSERT Statement
@@ROWCOUNT will give number of rows affected by recent DML
--
Chandu |
 |
|
|
James K
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 02/20/2013 : 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 here
SET @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
152 Posts |
Posted - 02/20/2013 : 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 = @wEtape
PRINT @@ROWCOUNT
is returns 0 (why), at least one line is inserted
return 0
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 02/20/2013 : 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
152 Posts |
Posted - 02/20/2013 : 09:57:11
|
| I checked the table directly in the database |
|
|
|
wided
Posting Yak Master
152 Posts |
Posted - 02/20/2013 : 10:18:59
|
it insert this in my table(U41_TachesWorkFlowDetail)
26 28 35035 CH
27 28 35035 DRH
I need to know if inserted a row to stop processing
just
26 28 35035 CH
after insert, i do this:
if @@ROWCOUNT > 0 break
I would like to exit the loop when inserting a row in this table
Are there any other means |
|
|
|
James K
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 02/20/2013 : 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
152 Posts |
Posted - 02/21/2013 : 04:16:16
|
Hi James
WHILE @Status=0
BEGIN
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 --while
This is my query
I added one which is flag = 1 if @@ rowcount> 0 (thus recording added)
Then I added:
IF @ flag <> 0 BREAK
I put "print" in my procedure
the result:
wEtape = CH
flag =1
wEtape = DRH
flag = 1
normally the procedure should stop the insersion the "CH"
but the system inserts CH and DRH
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1391 Posts |
Posted - 02/21/2013 : 04:30:02
|
Where are you assigning flag value at first time?
--
Chandu |
|
|
| |
Topic |
|
number of rows inserted
