public static int MassEmpNumUpdate(string empToUpdateFrom, string empToUpdateTo)
{
string sql;
int retval;
using (cn = new SqlConnection(ConnectionString()))
{
cn.Open();
sql = "uspUpdateDet"; // THIS IS THE 1ST SP
using (cmd = new SqlCommand(sql, cn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpToUpdateFrom", empToUpdateFrom);
cmd.Parameters.AddWithValue("@EmpToUpdateTo", empToUpdateTo);
cmd.Parameters.Add("@ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
retval = (int)cmd.Parameters["@ReturnValue"].Value;
}
if (retval == 0)
{
sql = "uspUpdatePrev"; // THIS IS THE 2ND SP - not working :(
using (cmd = new SqlCommand(sql, cn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpToUpdateFrom", empToUpdateFrom);
cmd.Parameters.AddWithValue("@EmpToUpdateTo", empToUpdateTo);
cmd.Parameters.Add("@returnvalue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
return cmd.ExecuteNonQuery();
}
}
return retval;
}
}
}
I have 2 stored procedure (that will update 2 tables) the uspUpdateDet and uspUpdatePrev. My question is why is my second (uspUpdatePrev) statement is not working. btw the sp is working right from sql server 08.
SP1:
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[uspUpdateDet]
@EmpToUpdateFrom varchar(7)
, @EmpToUpdateTo varchar(7)
AS SET NOCOUNT ON;
DECLARE @affectedRows int SET @affectedRows = 0;
BEGIN UPDATE tbl1 SET DET1 = @EmpToUpdateTo WHERE DET1 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END
BEGIN UPDATE tbl1 SET DET2 = @EmpToUpdateTo WHERE DET2 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END
BEGIN UPDATE tbl1 SET L1 = @EmpToUpdateTo WHERE L2 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END
BEGIN UPDATE tbl1 SET L2 = @EmpToUpdateTo WHERE L2 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END
BEGIN UPDATE tbl1 SET L3 = @EmpToUpdateTo WHERE L3 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END
BEGIN UPDATE tbl1 SET L4 = @EmpToUpdateTo WHERE L4 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END
BEGIN UPDATE tbl1 SET L5 = @EmpToUpdateTo WHERE L5 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END
BEGIN UPDATE tbl1 SET L6 = @EmpToUpdateTo WHERE L6 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END
BEGIN UPDATE tbl1 SET L7 = @EmpToUpdateTo WHERE L7 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END
BEGIN UPDATE tbl1 SET L8 = @EmpToUpdateTo WHERE L8 = @EmpToUpdateFrom SET @affectedRows = @affectedRows + @@ROWCOUNT END
return @affectedRows