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 id,string name,int id1,string 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

How to Assign two stored procedure

Answers (3)