January 31, 2011 at 11:11 pm
Hi All,
I've a simple SP, just insert, and output number of row been added.
my .net app call this SP and get the output back to the .net app
the data store in a datatable, once button begin click, then run this SP
create procedure usp_insert4] (
@firstName as nchar(30),
@lastName as nchar(30),
@address1 as nchar(20),
@address2 as nchar(30),
@address3 as nchar(30),
@city as nchar(20),
@insertRowCount as int output
)
AS
INSERT INTO temp_tblCalledList(
firstName,
lastName,
address1,
address2,
address3,
city
)
VALUES(
@firstName,
@lastName,
@address1,
@address2,
@address3,
@city
)
select @insertRowCount=@@rowcount;
my question is:
How the SP insert all those row to the database, and return rowcount?
As I know, the SP will insert one row at a time, so how the process know how many row been insert?
Does the SP will repeat the insert statment until all the row been insert?
Or Does the SP itself will repeat until all the row been insert?
Iam confus with the concept, any can give me some explaination 🙂
February 1, 2011 at 12:16 am
The procedure will insert only one row every time it's called, and @@ROWCOUNT will be 1. If you want to insert multiple rows, you need to execute the procedure multiple times. However, you won't be able to keep track of the total number of inserted rows without modifying the SP; would it be possible for the application to track the number of rows?
February 1, 2011 at 12:25 am
Reply is:
How the SP insert all those row to the database, and return rowcount? -> SP will insert a single row at a point of time and it returns rowcount 1 if succeed.
As I know, the SP will insert one row at a time, so how the process know how many row been insert?-> To insert the morethan one row you need to execute/call the SP multiple time, and allways it will return you 1 so to get the total number of row inserted you need to maintain a counter.
Does the SP will repeat the insert statment until all the row been insert? Or Does the SP itself will repeat until all the row been insert? -> SP will not repeat itself to insert the morethan one row, it needs to be repeat to insert the morethan one row.
February 1, 2011 at 4:21 pm
the point is the output variable return the number of row been insert is 500.
my .net app only return the @insertRowCount one time, that is how many rows been added
there is where the idea from
http://www.sqlteam.com/article/stored-procedures-returning-data
go down to Using Return
I just replace the select to inert, and it actualy return what I want, then make me confus, and that is why I ask this question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply