How to Assign two stored procedure

Question

How to assign two stroed procedure in a transaction strSQL = "Update TableOne set dbalance = dBalance - " + Amt.Text + " where AccNumber = '" + txtFrom.Text + "'"; cmd.CommandText=strSQL; cmd.ExecuteNonQuery(); strSQL = "Update TableTwo set dbalance = dBalance + " + Amt.Text + " where AccNumber = '" + txtTo.Text + "'"; cmd.CommandText = strSQL; cmd.ExecuteNonQuery(); Above is using two Sql statements in a transaction. I want to use two SP in a transaction. HOw to do? Help greatly apprecitated Thanks in advance

aryaonmesh · Answer

Here What i am doing and it's working public void UpdateDummyTest(int idstring nameint id1string name1) { // Create Instance of Connection and Command Object SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings[connectionString]); myConnection.Open(); SqlTransaction trans = myConnection.BeginTransaction(); try { //inline calling of a stored procedure //string str=UpdateDummyTest + id + ' + name +  ' ; SqlCommand myCommand = new SqlCommand(UpdateDummyTest myConnection); myCommand.CommandType=CommandType.StoredProcedure; myCommand.Transaction = trans; // Add Parameters to SPROC SqlParameter parameterID = new SqlParameter(@id SqlDbType.Int 4); parameterID.Value = id; myCommand.Parameters.Add(parameterID); SqlParameter parameterName = new SqlParameter(@name SqlDbType.NVarChar 50); parameterName.Value = name; myCommand.Parameters.Add(parameterName); myCommand.ExecuteNonQuery(); //If you doesn't clear parameter here you will get error as // Procedure or function UpdateDummyTest2 has too many arguments specified myCommand.Parameters.Clear(); myCommand.CommandText=UpdateDummyTest2; SqlParameter parameterID1 = new SqlParameter(@id1 SqlDbType.Int 4); parameterID1.Value = id1; myCommand.Parameters.Add(parameterID1); SqlParameter parameterName1 = new SqlParameter(@name1 SqlDbType.NVarChar 50); parameterName1.Value = name1; myCommand.Parameters.Add(parameterName1); myCommand.ExecuteNonQuery(); trans.Commit(); Label.Text=Transaction Success; } catch(SqlException ex) { trans.Rollback(); Label.Text=ex.Message(); } finally { myConnection.Close(); } }

aryaonmesh · Answer

Thanks! ifran Here i have given two sql stmts as same but actually they are two different sql smts with different paramerters Yeah  as you said i can write two in one but what if i want to use two sp and then assign to Command object for transaction. Thanks Uday

madinewala · Answer

One thing you could do is club the above two SQL stmts into a single stored proc and use it in your code. I say this because I do not see any conditional stmts. for executing the 2nd SQL qry. That means both these queries are independent of each other. Best is to use only a single SP that has both the above SQL Stmts. HTH irfan