[SQL]向3个表插入数据的存储过程 和 C# 代码

 public int UpdateQty(string strPartID, int iQty, int iUpdateQty, string strBarCode, string strCreaterId)
        {
            int iRet;
            SqlConnection conn = DBConnection.GetConn_SPAIS();
            SqlCommand cmd = new SqlCommand("CSP0203_UpdateQty", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@vcPartID", strPartID);
            cmd.Parameters.AddWithValue("@Qty", iQty);
            cmd.Parameters.AddWithValue("@updateQty", iUpdateQty);
            cmd.Parameters.AddWithValue("@vcBarCode", strBarCode);
            cmd.Parameters.AddWithValue("@vcCreaterId", strCreaterId);
            cmd.Parameters.Add("return", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                iRet = (int)cmd.Parameters["return"].Value;
                return iRet;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
                
            }
            
        }

use SPAISdb

go
 alter proc CSP0203_UpdateQty
    
    @vcPartID varchar(50)
   ,@Qty int
   ,@updateQty int
   ,@vcBarCode varchar(50)
   ,@vcCreaterId varchar(50)
   --,@Return int output

   
   
 as
 begin  transaction 
    update tStock
        set iStockQty=iStockQty-@Qty+@updateQty 
        where vcPartID=@vcPartID
        
    update tScan
        set decQuantity=decQuantity-@Qty+@updateQty ,decStockNum=decStockNum-@Qty+@updateQty 
        where vcBarCode=@vcBarCode
        
    insert into tUpdateQtyRecord(vcBarCode,vcPartID,iUpdateQty,vcCreaterId)values(@vcBarCode,@vcPartID,@updateQty,@vcCreaterId)
    
 if @@error<>0 
 begin
    rollback transaction
    --set @Return=1
    return -1
 end
 else
 begin
    commit transaction
 end