多个参数Sqlserver SP调用

dts = new DataTable();
        dts.Columns.Add("Code", Type.GetType("System.String"));
        dts.Columns.Add("Data", Type.GetType("System.String"));

 

        dts.Rows.Add(new object[] { "SPType", "640" });
        dts.Rows.Add(new object[] { "CallID", GridView_packcs.Rows[e.RowIndex].Cells[2].Text.Trim() });
        dts.Rows.Add(new object[] { "Editor", userid.Trim().ToUpper() });
        SqlConnection conn = new SqlConnection(ReportStr);
        try
        {
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = "dbo.usp_PackingCallMaterial_Save";
            cmd.CommandType = CommandType.StoredProcedure;

 

            cmd.Parameters.Add(new SqlParameter("@InPutData", SqlDbType.Structured));
            cmd.Parameters.Add(new SqlParameter("@RetnCode", SqlDbType.VarChar, 1));
            cmd.Parameters.Add(new SqlParameter("@RetnInfo", SqlDbType.VarChar, 50));

 

            cmd.Parameters["@InPutData"].Value = dts;
            cmd.Parameters["@RetnCode"].Direction = ParameterDirection.Output;
            cmd.Parameters["@RetnInfo"].Direction = ParameterDirection.Output;

            cmd.ExecuteNonQuery();
            string fd = cmd.Parameters["@RetnInfo"].Value.ToString().Trim();
            cmd.Dispose();
            Response.Write("<script>alert('" + fd + "');</script>");
        }
        catch (Exception i)
        {
            Response.Write("<script>alert('" + i.Message + "');</script>");
            //Response.Write("<script>alert('保存至數據庫失敗');</script>");
        }
        finally
        {
            conn.Close();
        }

 

 

SP主要代码

 

USE [SCC]
GO

 

/****** Object:  StoredProcedure [dbo].[usp_PackingCallMaterial_Save]    Script Date: 7/9/2019 8:27:17 AM ******/
SET ANSI_NULLS ON
GO

 

SET QUOTED_IDENTIFIER ON
GO

 

/*============================================================================================================================
 2019011101  2019/01/11  mhx  倉庫叫料系統數據保存

 

 declare  @InPutData  CodeDataType , @RetnCode varchar(1),@RetnInfo varchar(100)
 insert @InPutData select 'SPType','100'
 insert @InPutData select 'Area','100'
 insert @InPutData select 'PdLine','100'
 exec usp_PackingCallMaterial_Save @InPutData, @RetnCode output,@RetnInfo output
 select @RetnCode  ,@RetnInfo
===========================================================================================================================*/
 

 

CREATE Proc [dbo].[usp_PackingCallMaterial_Save]  @InPutData  CodeDataType READONLY  , @RetnCode varchar(1) output, @RetnInfo nvarchar(50) output  AS

 

declare
 @SPType  varchar(4) ,  --保存那個界面的數據
 @Area  nvarchar(10) ,  -- 區域
 @PdLine  varchar(5),   -- 線別
 @Model  varchar(12),  -- WB 機型
 @Type  varchar(10),  --@Model 的Type
 @Pn   varchar(12),  -- 包材料號
 @BOMQty  float,    -- BOM 中包材用量
 @PO   varchar(20),  -- FIS 工單
 @CallID  varchar(20),  -- 叫料ID
 @QTY  int,    --叫料套數
 @Editor  varchar(10),
 @PlantCode  varchar(5),         --倉別
 @PoQty  int,    -- FIS 工单的Qty
 @DataId  int

 

 select @SPType=Data from @InPutData where Code='SPType' 
 select @Area=Data from @InPutData where Code='Area' 
 select @PdLine=Data from @InPutData where Code='PdLine' 
 select @Model=Data from @InPutData where Code='Model' 
 select @Type=Data from @InPutData where Code='Type' 
 select @Pn=Data  from @InPutData where Code='Pn' 
 select @BOMQty=Data from @InPutData where Code='BOMQty' 
 select @PO =Data from @InPutData where Code='PO' 
 select @PoQty=Data from @InPutData where Code='PoQty' 
 select @CallID=Data from @InPutData where Code='CallID' 
 select @QTY =Data from @InPutData where Code='QTY' 
 select @Editor=Data from @InPutData where Code='Editor' 
 select @PlantCode=Data from @InPutData where Code='PlantCode' 
  

 

if   @SPType not in ('100','200','300','400','500','501','600','610','620','630','640','700','710','800','900') begin
  select @RetnCode=  '1',@RetnInfo=N'未知界面'
  return
end

 

if(@SPType='100')  begin-- 區域維護資料保存
 if @PdLine='' or  @Area='' or @PdLine='' or @Editor=''  begin
  select @RetnCode=  '1',@RetnInfo=N'数据不能为空'
  return
 end
 if exists (select 1 from MFG_Area_Line where PdLine=@PdLine AND InUse='1' ) begin
  select @RetnCode=  '1',@RetnInfo=@PdLine+N'已維護區域'
  return
 end
 else begin
  insert MFG_Area_Line (Area ,PdLine ,Editor ,InUse,Cdt,Udt) values(@Area,@PdLine,@Editor,'1',GETDATE(),getdate())
  select @RetnCode='0',@RetnInfo=@PdLine+'數據保存成功'
  return
 end
end

 

if(@SPType='200') begin  -- BOM PARTS 維護
 if (@Model='' or  @Type='' or @Editor='') begin
  select @RetnCode=  '1',@RetnInfo=N'數據不能未空'
  return
 end
 if exists (select 1 from SCC.dbo.MFG_Packing_BomParts where PN=@Model and InUse='1') begin
  select @RetnCode=  '1',@RetnInfo=@Model+'已維護'
  return
 end
 insert SCC.dbo.MFG_Packing_BomParts (PN,Type,Descr,InUse,Editor,Cdt,Udt) Values( @Model,@Type,'','1',@Editor,getdate(),getdate())
 select @RetnCode=  '1',@RetnInfo=@Model+'數據保存成功'
 return
end
if(@SPType='300') begin --維護Bom關係 SP(300)
 if (@Model='' or  @Pn='' or @BOMQty='' or @Editor='' or @PlantCode='') begin
  select @RetnCode=  '1',@RetnInfo=N'數據不能未空'
  return
 end

 

 if exists(select 1 from SCC..MFG_Packing_Bom where InUse ='1' and Model=@Model and PN=@Pn) begin
  select @RetnCode=  '1',@RetnInfo=@Model+'的'+@Pn+'已維護'
  return
 end
 insert SCC..MFG_Packing_Bom(Model,PN,Qty,InUse,Editor ,Cdt,Plant ) values(@Model,@Pn,@BOMQty,'1',@Editor,getdate(),@PlantCode)
end

 

if(@SPType='400') begin  --產線包材需求输入

 

 if ( @Model='' or @PO='' or @PoQty='' or @QTY='') begin
  select @RetnCode=  '1',@RetnInfo=N'數據不能未空'
  return
 end

 

 if exists (select 1 from  MFG_CallId_Status where Status='10') begin
  select @CallID=CallId FROM  MFG_CallId_Status where Status='10'  
 end
 else begin
  select @CallID=convert(varchar,getdate(),112)+ replace(convert(varchar,getdate(),114) ,':','')
  insert MFG_CallId_Status_Log values( @CallID,'10',getdate())
  insert MFG_CallId_Status values(@CallID ,'10',getdate(),getdate())
 end
 
 
 if exists (select 1 from MFG_Call_PackingMaterial where WkNo=@PO) begin
  select @PoQty=Qty from PCA..PCA_LOT WHERE PcaNo=@PO   
  if ((select sum(WkNo_CallQty)+@QTY  from MFG_Call_PackingMaterial where WkNo=@PO)>@PoQty ) begin
    select @RetnCode='0',@RetnInfo=@PO+'叫料數量已超過工單總數量!!'
    return
  end
  end 

 

  insert MFG_Call_PackingMaterial select @CallID,@Model,@PO,@PoQty,'',@QTY,'1',@Editor,getdate(),getdate()  
  select @DataId=max(DataId) from MFG_Call_PackingMaterial

 

  insert MFG_Call_PackingMaterial_Detail
  select DataId,@PdLine,@Model,@PO,'0',b.PN,b.Qty,@QTY, CEILING(b.Qty*@QTY),'1',@Editor ,GETDATE() from MFG_Call_PackingMaterial a, MFG_Packing_Bom b where  a.Model=b.Model
  and  CallId =@CallID and a.Model=@Model and WkNo = @PO and WkNo_CallQty =@QTY and a.InUse ='1'and b.InUse ='1' and a.DataId =@DataId
     
  select @RetnCode=  '1',@RetnInfo=@Model+'數據保存成功!!'
  return
 
end

 

if(@SPType ='500') begin --倉庫包材備料sp 500
 if (@CallID='' ) begin
   select @RetnCode=  '1',@RetnInfo=N'数据不能为空'
   return
  end

 

  update dbo.MFG_CallId_Status set Status='2',Udt=getdate() where CallId=@CallID
  insert MFG_CallId_Status_Log values( @CallID,'2',getdate())
 
  /*20190612 www 倉庫要求初始備料數量同叫料數量一致，減少輸入*/
  UPDATE MFG_Stock_PackingMaterial SET Stock_Qty=Call_Qty WHERE CallId=@CallID

 

  --insert MFG_Stock_PackingMaterial(CallId,Area,PN,Call_Qty,Stock_Qty,Editor,InUse ,Cdt,Udt)
  --SELECT b.CallId,c.Area,a.PN,SUM(a.Call_Qty)Call_Qty,'','FIS','1',getdate(),getdate() FROM
  -- MFG_Call_PackingMaterial_Detail a,MFG_Call_PackingMaterial b,dbo.MFG_Area_Line c WHERE b.CallId=@CallID and a.DataId=b.DataId and a.Line=c.PdLine and c.InUse=1 and a.InUse=1 and b.InUse=1
  -- GROUP BY a.PN,b.CallId,c.Area ORDER BY c.Area,a.PN
 --MFG_Call_PackingMaterial_Detail

 

 select @RetnCode=  '1',@RetnInfo=@Model+'數據保存成功'
 return
END

 

if(@SPType ='501') begin --倉庫包材備料完成sp 501
 if (@CallID='' ) begin
   select @RetnCode=  '1',@RetnInfo=N'数据不能为空'
   return
  end

 

 DECLARE @station VARCHAR(5)
 SELECT @station=Status FROM MFG_CallId_Status where CallId=@CallID

 

 IF @station='2' BEGIN
  CREATE TABLE #wip
  (
  Area VARCHAR(10),
  PN VARCHAR(15),
  diff INT
  )

 

  INSERT #wip
  SELECT Area,PN,SUM((Stock_Qty-Call_Qty))diff FROM MFG_Stock_PackingMaterial WHERE (Stock_Qty-Call_Qty)<>'0' AND CallId=@CallID GROUP BY Area,PN

 

  UPDATE MFG_Call_PackingMaterial_Diff SET Diff=a.Diff+b.diff,Udt=GETDATE() FROM MFG_Call_PackingMaterial_Diff a, #wip b WHERE a.Area=b.Area and a.PN=b.PN and a.InUse='1'

 

  DELETE #wip FROM MFG_Call_PackingMaterial_Diff a, #wip b WHERE a.Area=b.Area and a.PN=b.PN and a.InUse='1'

 

  INSERT MFG_Call_PackingMaterial_Diff (Area,PN,Diff,Editor,InUse,Cdt,Udt)
  SELECT Area,PN,diff,'FIS','1',GETDATE(),GETDATE() FROM #wip
 END

 

  update dbo.MFG_CallId_Status set Status='3',Udt=getdate() where CallId=@CallID
  insert MFG_CallId_Status_Log values( @CallID,'3',getdate())
 
 select @RetnCode=  '1',@RetnInfo=@Model+'數據保存成功'
 return
END

 

 

 

if(@SPType ='610') begin --產線包材叫料&領料界面sp 600  中的(查询)
 if exists (select * from MFG_CallId_Status where CallId=@CallID and Status =10) begin
   insert MFG_Stock_PackingMaterial(CallId,Area,PN,Call_Qty,Stock_Qty,Editor,InUse ,Cdt,Udt)
   SELECT b.CallId,c.Area,a.PN,SUM(a.Call_Qty)Call_Qty,'','FIS','1',getdate(),getdate() FROM
   MFG_Call_PackingMaterial_Detail a,MFG_Call_PackingMaterial b,dbo.MFG_Area_Line c WHERE b.CallId=@CallID and a.DataId=b.DataId and a.Line=c.PdLine and c.InUse=1 and a.InUse=1 and b.InUse=1
   GROUP BY a.PN,b.CallId,c.Area ORDER BY c.Area,a.PN
 end
 return
end

 

if(@SPType ='620') begin --產線包材叫料&領料界面sp 600  中的(叫料)
 if not exists (select 1 from MFG_CallId_Status where  CallId=@CallID and Status ='10') begin
 select @RetnCode=  '1',@RetnInfo=@CallID+N'狀態不為10'
 return
 end
  update  MFG_CallId_Status set Status='1' where CallId=@CallID
  insert MFG_CallId_Status_Log values(@CallID,'1',getdate()) 
  select @RetnCode=  '1',@RetnInfo='數據保存成功'
 return
end

 

if(@SPType ='630') begin --產線包材叫料&領料界面sp 600  中的(領料)
 if not exists (select 1 from MFG_CallId_Status where  CallId=@CallID and Status ='3') begin
 select @RetnCode=  '1',@RetnInfo=@CallID+N'状态不为3'
 return
 end
  update  MFG_CallId_Status set Status='4' where CallId=@CallID
  insert MFG_CallId_Status_Log values(@CallID,'4',getdate()) 
  select @RetnCode=  '1',@RetnInfo='數據保存成功'
 return
end

 

if(@SPType ='640') begin --產線包材叫料&領料界面sp 600  中的(删除)
 if not exists (select 1 from MFG_CallId_Status where  CallId=@CallID and Status ='5') begin
 select @RetnCode=  '1',@RetnInfo=@CallID+N'狀態不為退回'
 return
 end

 

  update  MFG_CallId_Status set Status='0' where CallId=@CallID
  insert MFG_CallId_Status_Log values(@CallID,'0',getdate()) 
  select @RetnCode=  '1',@RetnInfo='数据删除成功'
 return
end

 

if(@SPType ='700') begin --產線材料WIP 700(查询按钮）
 if @Pn='' begin
  select * from dbo.MFG_Call_PackingMaterial_Diff where InUse ='1'
 end
 else begin
  select * from dbo.MFG_Call_PackingMaterial_Diff where PN=@Pn and InUse ='1'
 end
end
if(@SPType ='710') begin --產線材料WIP 700(新增加按钮）
 
 insert MFG_Call_PackingMaterial_Diff values(@Area,@Pn ,@QTY,@Editor ,'1',getdate(),getdate()) 
 select @RetnCode=  '1',@RetnInfo='OK'
 return
end

 

if(@SPType ='800') begin --產線倉庫叫料備料看板 800 

 

 
 select @RetnCode=  '1',@RetnInfo=@Model+'數據保存成功'
  return 
end

 

if(@SPType ='900') begin --叫料备料信息查询 900
 -- 1395T Model
 if left(@Type,2)<>'WB'  begin

 

 SELECT top 1  a.CallId,a.Area,a.PN,a.Call_Qty,a.Stock_Qty,b.Status,a.Editor,a.Cdt,a.Udt FROM dbo.MFG_Stock_PackingMaterial a, dbo.MFG_CallId_Status b
 WHERE a.CallId=@CallID AND a.InUse=1 AND a.CallId=b.CallId
 end
 else if left(@Type,2)='WB' begin
 -- WB Model
 SELECT top 1  b.CallId,c.Area,a.DataId,a.Model,a.WkNo,a.PN,b.WkNo_Qty,a.[Set],SUM(a.Call_Qty)Call_Qty FROM MFG_Call_PackingMaterial_Detail a,MFG_Call_PackingMaterial b,dbo.MFG_Area_Line c WHERE b.CallId='2018121901'
 AND a.DataId=b.DataId AND a.Line=c.PdLine AND c.InUse=1 AND a.InUse=1 AND b.InUse=1 and CallId =@CallID
 GROUP BY a.PN,b.CallId,c.Area,a.Model,a.DataId,a.WkNo,b.WkNo_Qty,a.[Set] ORDER BY a.DataId, a.Model,a.PN
 end

 

 select @RetnCode='1',@RetnInfo=@Model+'數據保存成功'
 return
end

 

 
GO