SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 number of rows inserted
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wided
Posting Yak Master

199 Posts

Posted - 02/20/2013 :  09:02:34  Show Profile  Reply with Quote
Following a request insert, I would like to retrieve the number of rows inserted

thank you

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 02/20/2013 :  09:09:48  Show Profile  Reply with Quote
INSERT Statement
@@ROWCOUNT will give number of rows affected by recent DML

--
Chandu
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 02/20/2013 :  09:10:52  Show Profile  Reply with Quote
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.
Go to Top of Page

wided
Posting Yak Master

199 Posts

Posted - 02/20/2013 :  09:23:11  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 02/20/2013 :  09:41:01  Show Profile  Reply with Quote
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?
Go to Top of Page

wided
Posting Yak Master

199 Posts

Posted - 02/20/2013 :  09:57:11  Show Profile  Reply with Quote
I checked the table directly in the database
Go to Top of Page

wided
Posting Yak Master

199 Posts

Posted - 02/20/2013 :  10:18:59  Show Profile  Reply with Quote

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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 02/20/2013 :  11:12:14  Show Profile  Reply with Quote
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
Go to Top of Page

wided
Posting Yak Master

199 Posts

Posted - 02/21/2013 :  04:16:16  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 02/21/2013 :  04:30:02  Show Profile  Reply with Quote
Where are you assigning flag value at first time?

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000