【SqlServer存储过程（二维数组参数）调用】

本文主要是介绍【SqlServer存储过程（二维数组参数）调用】，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

外部调用存储过程方式： 

_SCLLID --存储过程名 @ICStockBill='-1,345,2021-12-05,8185,7403,349,administrator',--表头数组参数(用,分割) 
/*** @ICStockBillEntry='1443,2021120100071,1100,40,751||1443,2021120100068,1100,40,734||1369,2021120100045,980,40,718.8||1369,2021120100037,980,40,717.4||1389,2021112300105,980,40,100.2||1389,2021112300106,980,40,100'--表体二维数组参数(用||和,分割) ***/
@ICStockBillEntry='1443,2021120100071,1100,40,751'--表体一维数组参数(用,分割)

-- =============================================
-- Author:		zjh
-- Create date: 2021-12-05
-- Description:	生产领料
-- ================== 以下是存储过程的内容：========================
CREATE PROCEDURE [dbo].[_SCLLID]
@ICStockBill varchar(100),
@ICStockBillEntry varchar(1500)
AS
BEGIN	
SET NOCOUNT ON;
declare @p2 int
exec GetICMaxNum 'ICStockBill',@p2 output,1,16394
declare @BillNo varchar(20)
declare @ftrantype varchar(20)
set @ftrantype = 24--领料单
update ICBillNo set FCurNo=FCurNo+1 where FBillID=@FTranType			set @BillNo=(select FFormat from ICBillNo where FBillID= @FTranType)			declare @lenBm int
set @LenBm=len(@BillNo)

			set @BillNo=right(@BillNo+cast((select FCurNo from ICBillNo where FBillID= @FTranType) as varchar(12)),@LenBm)

			set @BillNo=(select FPreLetter from ICBillNo where FBillID= @FTranType)+@BillNo

declare @tmpindex int = 1

declare @oldindex int = 1

declare @m1 int

set @tmpindex=CHARINDEX(',',@ICStockBill)

set @m1=cast(SUBSTRING(@ICStockBill,@oldindex,@tmpindex-@oldindex) as int)

set @oldindex=@tmpindex

declare @m2 int

set @tmpindex=CHARINDEX(',',@ICStockBill,@oldindex+1)

set @m2=cast(SUBSTRING(@ICStockBill,@oldindex+1,@tmpindex-@oldindex-1) as int)

set @oldindex=@tmpindex

declare @m3 char(10)

set @tmpindex=CHARINDEX(',',@ICStockBill,@oldindex+1)

set @m3=SUBSTRING(@ICStockBill,@oldindex+1,@tmpindex-@oldindex-1)

set @oldindex=@tmpindex

declare @m4 int

set @tmpindex=CHARINDEX(',',@ICStockBill,@oldindex+1)

set @m4=cast(SUBSTRING(@ICStockBill,@oldindex+1,@tmpindex-@oldindex-1) as int)

set @oldindex=@tmpindex

declare @m5 int

set @tmpindex=CHARINDEX(',',@ICStockBill,@oldindex+1)

set @m5=cast(SUBSTRING(@ICStockBill,@oldindex+1,@tmpindex-@oldindex-1) as int)

set @oldindex=@tmpindex

declare @stockid int

set @tmpindex=CHARINDEX(',',@ICStockBill,@oldindex+1)

set @stockid=cast(SUBSTRING(@ICStockBill,@oldindex+1,@tmpindex-@oldindex-1) as int)

declare @m6 int

set @m6=(select FUserID from t_user where FName=SUBSTRING(@ICStockBill,@tmpindex+1,LEN(@ICStockBill)-@tmpindex))

begin tran

begin try

INSERT INTO ICStockBill(FInterID,FBillNo,FBrNo,FTranType,FCancellation,FStatus,FUpStockWhenSave,FROB,FHookStatus,

	Fdate,FDeptID,Fuse,FCheckDate,FSManagerID,FFManagerID,FBillerID,FAcctID,FMultiCheckDate1,FMultiCheckDate2,

	FMultiCheckDate3,FMultiCheckDate4,FMultiCheckDate5,FVchInterID,FMultiCheckDate6,FPurposeID,FWBINTERID,

	FSelTranType,FBackFlushed,FManageType,FPrintCount)

values(@p2,@BillNo,'0',24,0,0,0,@m1,0,@m3,@m2,'',Null,@m4,@m5,@m6,0,Null,Null,Null,Null,Null,0,Null,12000,0,1,0,0,0)

set @oldindex=-1

set @tmpindex=CHARINDEX('||', @ICStockBillEntry,@oldindex+2)

declare @m21 int

declare @m22 varchar(28)

declare @m23 varchar(28)

declare @m24 varchar(28)

declare @m25 decimal(13,1)

declare @tmpindex2 int = 1

declare @oldindex2 int = 1

if (@tmpindex > 0)

 BEGIN

  declare @tmpentry varchar(80)

  declare @cnt int = 1

  WHILE (@tmpindex > 0)

    BEGIN

      BEGIN

		set @tmpentry=SUBSTRING(@ICStockBillEntry, @oldindex+2, @tmpindex-@oldindex-2)

        set @tmpindex2=CHARINDEX(',',@tmpentry)

		set @m21=cast(SUBSTRING(@tmpentry,@oldindex2,@tmpindex2-@oldindex2) as int)

		set @oldindex2=@tmpindex2

		set @tmpindex2=CHARINDEX(',',@tmpentry,@oldindex2+1)

		set @m22=SUBSTRING(@tmpentry,@oldindex2+1,@tmpindex2-@oldindex2-1)

		set @oldindex2=@tmpindex2

		set @tmpindex2=CHARINDEX(',',@tmpentry,@oldindex2+1)

		set @m23=SUBSTRING(@tmpentry,@oldindex2+1,@tmpindex2-@oldindex2-1)

		set @oldindex2=@tmpindex2

		set @tmpindex2=CHARINDEX(',',@tmpentry,@oldindex2+1)

		set @m24=SUBSTRING(@tmpentry,@oldindex2+1,@tmpindex2-@oldindex2-1)	

		set @m25=cast(SUBSTRING(@tmpentry,@tmpindex2+1,LEN(@tmpentry)-@tmpindex2) as decimal(13,1))

		set @tmpindex2=1

		set @oldindex2=1

		INSERT INTO ICStockBillEntry (FInterID,FEntryID,FBrNo,FItemID,FAuxPropID,FBatchNo,

			FQtyMust,FQty,FReProduceType,FCostOBJID,FCostObjGroupID,FUnitID,FAuxQtyMust,Fauxqty,

			FSecCoefficient,FSecQty,FAuxPlanPrice,FPlanAmount,Fauxprice,Famount,FBomInterID,Fnote,

			FKFDate,FKFPeriod,FPeriodDate,FIsVMI,FEntrySupply,FSCStockID,FEntrySelfB0457,

			FEntrySelfB0458,FDCSPID,FOperSN,FOperID,FSNListID,FSourceBillNo,FSourceTranType,

			FSourceInterId,FSourceEntryID,FICMOBillNo,FICMOInterID,FPPBomEntryID,FInStockID,

			FCostCenterID,FPlanMode,FMTONo,FPositionNo,FItemSize,FItemSuite,FDiscardID) 

		values(@p2,@cnt,'0',@m21,0,@m22,0,@m25,1059,0,0,275,0,@m25,0,0,0,0,0,0,0,'',Null,0,Null,0,0,

			@stockid,@m23,@m24,0,0,'0',0,'',0,0,0,'',0,0,0,0,14036,'','','','',0)

		set @cnt = @cnt + 1

      END

      SET @oldindex = @tmpindex

      set @tmpindex=CHARINDEX('||', @ICStockBillEntry,@oldindex+2)

      if (@tmpindex < 1)

        BEGIN

		set @tmpentry=SUBSTRING(@ICStockBillEntry, @oldindex+2, LEN(@ICStockBillEntry)-@oldindex-1)

        set @tmpindex2=CHARINDEX(',',@tmpentry)

		set @m21=cast(SUBSTRING(@tmpentry,@oldindex2,@tmpindex2-@oldindex2) as int)

		set @oldindex2=@tmpindex2

		set @tmpindex2=CHARINDEX(',',@tmpentry,@oldindex2+1)

		set @m22=SUBSTRING(@tmpentry,@oldindex2+1,@tmpindex2-@oldindex2-1)

		set @oldindex2=@tmpindex2

		set @tmpindex2=CHARINDEX(',',@tmpentry,@oldindex2+1)

		set @m23=SUBSTRING(@tmpentry,@oldindex2+1,@tmpindex2-@oldindex2-1)

		set @oldindex2=@tmpindex2

		set @tmpindex2=CHARINDEX(',',@tmpentry,@oldindex2+1)

		set @m24=SUBSTRING(@tmpentry,@oldindex2+1,@tmpindex2-@oldindex2-1)

		set @m25=cast(SUBSTRING(@tmpentry,@tmpindex2+1,LEN(@tmpentry)-@tmpindex2) as decimal(13,1))

		INSERT INTO ICStockBillEntry (FInterID,FEntryID,FBrNo,FItemID,FAuxPropID,FBatchNo,

			FQtyMust,FQty,FReProduceType,FCostOBJID,FCostObjGroupID,FUnitID,FAuxQtyMust,Fauxqty,

			FSecCoefficient,FSecQty,FAuxPlanPrice,FPlanAmount,Fauxprice,Famount,FBomInterID,Fnote,

			FKFDate,FKFPeriod,FPeriodDate,FIsVMI,FEntrySupply,FSCStockID,FEntrySelfB0457,

			FEntrySelfB0458,FDCSPID,FOperSN,FOperID,FSNListID,FSourceBillNo,FSourceTranType,

			FSourceInterId,FSourceEntryID,FICMOBillNo,FICMOInterID,FPPBomEntryID,FInStockID,

			FCostCenterID,FPlanMode,FMTONo,FPositionNo,FItemSize,FItemSuite,FDiscardID) 

		values(@p2,@cnt,'0',@m21,0,@m22,0,@m25,1059,0,0,275,0,@m25,0,0,0,0,0,0,0,'',Null,0,Null,0,0,

			@stockid,@m23,@m24,0,0,'0',0,'',0,0,0,'',0,0,0,0,14036,'','','','',0)		

      END

    END

  END

else

  BEGIN

	set @tmpindex2=CHARINDEX(',',@ICStockBillEntry)

	set @m21=cast(SUBSTRING(@ICStockBillEntry,@oldindex2,@tmpindex2-@oldindex2) as int)

	set @oldindex2=@tmpindex2

	set @tmpindex2=CHARINDEX(',',@ICStockBillEntry,@oldindex2+1)

	set @m22=SUBSTRING(@ICStockBillEntry,@oldindex2+1,@tmpindex2-@oldindex2-1)

	set @oldindex2=@tmpindex2

	set @tmpindex2=CHARINDEX(',',@ICStockBillEntry,@oldindex2+1)

	set @m23=SUBSTRING(@ICStockBillEntry,@oldindex2+1,@tmpindex2-@oldindex2-1)

	set @oldindex2=@tmpindex2

	set @tmpindex2=CHARINDEX(',',@ICStockBillEntry,@oldindex2+1)

	set @m24=SUBSTRING(@ICStockBillEntry,@oldindex2+1,@tmpindex2-@oldindex2-1)

	set @m25=cast(SUBSTRING(@ICStockBillEntry,@tmpindex2+1,LEN(@ICStockBillEntry)-@tmpindex2) as decimal(13,1))

	INSERT INTO ICStockBillEntry (FInterID,FEntryID,FBrNo,FItemID,FAuxPropID,FBatchNo,

		FQtyMust,FQty,FReProduceType,FCostOBJID,FCostObjGroupID,FUnitID,FAuxQtyMust,Fauxqty,

		FSecCoefficient,FSecQty,FAuxPlanPrice,FPlanAmount,Fauxprice,Famount,FBomInterID,Fnote,

		FKFDate,FKFPeriod,FPeriodDate,FIsVMI,FEntrySupply,FSCStockID,FEntrySelfB0457,

		FEntrySelfB0458,FDCSPID,FOperSN,FOperID,FSNListID,FSourceBillNo,FSourceTranType,

		FSourceInterId,FSourceEntryID,FICMOBillNo,FICMOInterID,FPPBomEntryID,FInStockID,

		FCostCenterID,FPlanMode,FMTONo,FPositionNo,FItemSize,FItemSuite,FDiscardID) 

	values(@p2,1,'0',@m21,0,@m22,0,@m25,1059,0,0,275,0,@m25,0,0,0,0,0,0,0,'',Null,0,Null,0,0,

		@stockid,@m23,@m24,0,0,'0',0,'',0,0,0,'',0,0,0,0,14036,'','','','',0)

  END

 end try

 begin catch

	rollback tran

	select 0

	return

 end catch

 commit tran

 select 1

END

这篇关于【SqlServer存储过程（二维数组参数）调用】的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！