Calling one stored procedure from another in C#

I ha开发者_JAVA技巧ve error 'Error converting data type nvarchar to decimal' when call stored procedure call another stored procedure from C# as

cmd = new SqlCommand("tax_Base_emp", con);
cmd.CommandType = CommandType.StoredProcedure;
parm1 = new SqlParameter("@emp_code", SqlDbType.BigInt); 
parm1.Value = emp_code; 
parm1.Direction = ParameterDirection.Input; 
cmd.Parameters.Add(parm1);
parm2 = new SqlParameter("@co_id", SqlDbType.BigInt); 
parm2.Value = Settings.Default.comp_id; 
parm2.Direction = ParameterDirection.Input; 
cmd.Parameters.Add(parm2);
parm3 = new SqlParameter("@d", SqlDbType.DateTime); 
parm3.Value = Convert.ToDateTime("31/1/2010"); 
parm3.Direction = ParameterDirection.Input; 
cmd.Parameters.Add(parm3);
parm4 = new SqlParameter("@y", SqlDbType.Int); 
parm4.Value =int.Parse(textBox2.Text); 
parm4.Direction = ParameterDirection.Input; 
cmd.Parameters.Add(parm4);
parm5 = new SqlParameter("@check_month", SqlDbType.Int); 
parm5.Value =1; 
parm5.Direction = ParameterDirection.Input; 
cmd.Parameters.Add(parm5);
parm6 = new SqlParameter("@month", SqlDbType.Int); 
parm6.Value =8; 
parm6.Direction = ParameterDirection.Input; 
cmd.Parameters.Add(parm6);
SqlParameter parm7 = new SqlParameter("@indate", SqlDbType.DateTime); 
parm7.Value = Convert.ToDateTime("8/5/2010"); 
parm7.Direction = ParameterDirection.Input; 
cmd.Parameters.Add(parm7);
SqlParameter parm8 = new SqlParameter("@Sumtotal", SqlDbType.Decimal); 
parm8.Scale = 2; 
parm8.Direction = ParameterDirection.Output; 
cmd.Parameters.Add(parm8);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
decimal tax_value = Convert.ToDecimal(cmd.Parameters["@Sumtotal"].Value);

And the stored proecdure called:

ALTER  PROCEDURE  Tax_Base_emp
  @emp_code bigint,
  @co_id bigint,
  @d datetime,
  @y int,
  @check_month int,
  @month int,
  @indate datetime,
  @Sumtotal decimal(8,2) output

AS
 declare  @tax_main_sal decimal(8,2)  
 declare  @tax_var_sal decimal(8,2)
 declare @salary decimal(8,2)
 declare @insh_varsalary decimal(8,2)
 declare @insh_value decimal(8,2)
 declare @vacation_value decimal(8,2)
 declare @vacation_varsalary decimal(8,2)
 declare @ded_value decimal(8,2) 
 declare @ben_value decimal(8,2) 


exec Taxable_mainsalary @emp_code,@co_id,@tax_main_sal output
exec taxable_varsalary @emp_code,@co_id, @tax_var_sal output
----taxableSalary---------------
set @salary=@tax_main_sal+@tax_var_sal
----insurance-------------------
exec  varsalary_insh @emp_code,@co_id,@d,@y, @insh_varsalary  output
exec insh_no @emp_code,@co_id,@insh_varsalary,@check_month, @insh_value output
----vacation--------------------
exec  vacation_varsalary @emp_code,@co_id,@vacation_varsalary output
exec  vacation_value @emp_code,@co_id,@y,@month,@vacation_varsalary,output
---------deduction--------------- 
exec deduction_for_tax @emp_code,@co_id,@indate,@ded_value output
-------------benifit------------
exec benfit_for_tax @emp_code,@co_id,@indate,@ben_value output
-----------------------------------NetSalary--------------------------------------------------------
 set @Sumtotal=(isnull(@salary,0)+isnull(@ben_value,0))-(isnull(@insh_value,0)+isnull(@vacation_value,0)+isnull(@ded_value,0))
return 

I don't see anything wrong with your C# code - it's really hard to tell what would be causing the problem. Your C# code is just simply calling a single stored proc - that shouldn't be a problem, really.

However, I do have a few recommendations for your coding style:

• put your SqlConnection and SqlCommand into using(....) { .... } blocks to make your code more reliable
• try to avoid specifying default property values, like Direction = ParameterDirection.Input; over and over again; the .Input is the default - only specify it when you deviate from that default
• if you do the same steps over and over and over again - why don't you put this in a method and call that method a couple of times?? This also saves you from having to create a gazillion of SqlParameter objects that you then just throw away .....

You'd end up with something like:

public void CallStoredProc()
{
    using(SqlConnection con = new SqlConnection(.....))
    using(SqlCommand cmd = new SqlCommand("tax_Base_emp", con))
    {
       cmd.CommandType = CommandType.StoredProcedure;

       AddParameter(cmd.Parameters, "@emp_code", SqlDbType.BigInt, emp_code);
       AddParameter(cmd.Parameters, "@co_id", SqlDbType.BigInt, comp_id);
       AddParameter(cmd.Parameters, "@d", SqlDbType.DateTime, Convert.ToDateTime("31/1/2010"));
       AddParameter(cmd.Parameters, "@y", SqlDbType.Int, int.Parse(textBox2.Text));
       AddParameter(cmd.Parameters, "@check_month", SqlDbType.Int, 1);
       AddParameter(cmd.Parameters, "@month", SqlDbType.Int, 8);
       AddParameter(cmd.Parameters, "@indate", SqlDbType.DateTime, Convert.ToDateTime("8/5/2010"));

       AddOutputParameter(cmd.Parameters, "@Sumtotal", SqlDbType.Decimal, 8, 2);

       con.Open();
       cmd.ExecuteNonQuery();
       con.Close();

       decimal tax_value = Convert.ToDecimal(cmd.Parameters["@Sumtotal"].Value);
   }
}

public void AddParameter(SqlParameterCollection params, string name, SqlDbType type, object value)
{
    SqlParameter tmpParam = new SqlParameter(name, type); 
    tmpParam.Value = value; 
    params.Add(tmpParam);
}

public void AddOutputParameter(SqlParameterCollection params, string name, SqlDbType type, int precision, int scale)
{
    SqlParameter tmpParam = new SqlParameter(name, type); 
    tmpParam.ParameterDirection = Direction.Output;
    tmpParam.Precision = precision; 
    tmpParam.Scale = scale; 
    params.Add(tmpParam);
}

I can't see anything in the code you have posted that would cause that.

I suspect that the error is in one of the 8 stored procedures you are calling where you are assigning an nvarchar to a decimal.

I'd comment these all out temporarily and just return a dummy number if that fixes it uncomment half and try again. If the error reoccurs then you know it is in one of the ones you just uncommented. i.e. do a binary search to find the offending procedure.