Calling a stored procedure recursively?
Hi, I have a stored procedure "InsertLineNumberSmallGroup" which insert rows into in sql server 2000 and i am using the C# code below to call the stored procedure which have 2 parameters.
It only managed to insert the first row and not anymore.
Why is this so?
-----------------------------------------------------------------------------------------------------------
SqlCommand SqlCmd = new SqlCommand("InsertLineNumberSmallGroup", conn);
SqlCmd.CommandType = CommandType.StoredProcedure;
SqlCmd.CommandTimeout = 1000;
SqlParameter SqlParam1 = SqlCmd.Parameters.Add("@L_LineNumber", SqlDbType.BigInt, 18);
SqlParameter SqlParam2 = SqlCmd.Parameters.Add("@L_OrderKey", SqlDbType.BigInt, 18);
Increment = 0;
temp = 10;
while (Increment != Temp)
{
Temp1 = Convert.ToInt32(dsLineNumber.Tables["LineNumberTemp"].Rows [Increment].ItemArray[0].ToString());
Temp2 = Convert.ToInt32(dsLineNumber.Tables["LineNumberTemp"].Rows[Increment].ItemArray[1].ToString());
SqlParam1.Value = Temp1;
SqlParam2.Value = Temp2;
conn.Open();
SqlCmd.ExecuteNonQuery();
conn.Close();
Increment++;
}
-----------------------------------------------------------------------------------------------------------
codes for the stored procedure in sql server2000:
-----------------------------------------------------------------------------------------------------------
CREATE PROCEDURE InsertLineNumberSmallGroup
@L_OrderKey bigint,
@L_LineNumber bigint
AS
INSERT INTO sunspot.createdTableLineNumberSmallGroupRows
SELECT * FROM sunspot.LINEITEM
WHERE L_OrderKey = @L_OrderKey AND L_LineNumber = @L_LineNumber
GO
----------------------------------------------------------------------------------------------------------
kovanPosted Mar 18, 2004, 10:47 AM