存储过程
CREATE proc TotalMoney
@SupplierName varchar(15),@ShouldPay money output,@SolidPay money output,@NoPay money output
as
select @ShouldPay=SUM(应收金额), @SolidPay=SUM(已收金额),@NoPay=SUM(应收金额)-SUM(已收金额) from 入库信息 where 供应商名称 like @SupplierName
GO
程序代码
try
{
cmd = new SqlCommand("TotalMoney",con);
con.Open();
cmd.CommandType=CommandType.StoredProcedure;
SqlParameter SupplierName=new SqlParameter("@SupplierName",SqlDbType.VarChar,15);
SupplierName.Value=""+txtSupplier.Text.Trim()+"%";
cmd.Parameters.Add(SupplierName);
cmd.Parameters.Add("@ShouldPay",SqlDbType.Money);
cmd.Parameters.Add("@SolidPay",SqlDbType.Money);
cmd.Parameters.Add("@NoPay",SqlDbType.Money);
cmd.Parameters["@ShouldPay"].Direction= ParameterDirection.Output;
cmd.Parameters["@SolidPay"].Direction= ParameterDirection.Output;
cmd.Parameters["@NoPay"].Direction= ParameterDirection.Output;
cmd.ExecuteNonQuery();
lblShouldPay.Text ="应付总金额: "+ string.Format("{0:¥###.##}",cmd.Parameters["@ShouldPay"].Value);
lblSolidPay.Text ="实付总金额: "+ string.Format("{0:¥###.##}",cmd.Parameters["@SolidPay"].Value);
lblNoPay.Text ="未付金额: "+ string.Format("{0:¥###.##}",cmd.Parameters["@NoPay"].Value);
con.Close();
}
catch (Exception Err)
{
MessageBox.Show(Err.ToString());
}