Update 2 tables in a procedure. Row count of one table unknown

[szlamany]

I have on several occasions had to pass the "detail rows" as a single VARCHAR parameter and decode them in the SPROC. That's a fine implementation.

See the parameter @POLINES

Create Procedure awc_ReqUserPO_Edit_Save
			 @Batch varchar(100)
			,@BatchNo varchar(100)
			,@ReleaseDt varchar(100)
			,@PONumber varchar(100)
			,@POCategory varchar(100)
			,@Vendor varchar(100)
			,@ShipTo varchar(100)
			,@SpecialInstr varchar(500)
			,@Notes varchar(500)
			,@UpdKey varchar(100)
			,@POLines varchar(max)
			,@username varchar(100)
As
Set NoCount On

Declare @FiscalYr int
Declare @BatchEntry int
Declare @POEntry int
Declare @CP int

Set @CP=CharIndex('/',@UpdKey)
Set @FiscalYr=Left(@UpdKey,@CP-1)
--Set @BatchEntry=Right(@UpdKey,Len(@UpdKey)-@CP)
Set @BatchEntry=(Select BatchEntry From Batch_T Where FiscalYr=@FiscalYr and BatchNo=@BatchNo and LedType='RQ')

Declare @SK varchar(100)
Set @SK='~save~'+@Batch

Declare @ErrMsg varchar(100)
Declare @POLinesWork Table (PORow int, POLine varchar(2), PODesc varchar(255), Acct varchar(30), Quantity money, UnitCost money, PercentSplit money, ExtendedCost money, LineCost money, LineNumber varchar(2)
					,SLASN varchar(4), AcctFY int, AcctTE int
					,GLFund varchar(2),GLSubFunc varchar(4),GLObject varchar(3),SLType varchar(1),SLFund varchar(2),SLFunc varchar(4),SLObj varchar(3),SLFundSrc varchar(3)
					,SLGrade varchar(2),SLBldg varchar(2),SLDept varchar(3),SLMisc varchar(4),SLYear varchar(2)
					,TransEntry int, POEntry int, AddRec int)

Declare @Row varchar(max)
Declare @Col varchar(max)
Declare @CR int
Declare @CC int
Declare @LCR int
Declare @LCC int

Declare @POLine varchar(max)
Declare @PODesc varchar(max)
Declare @Acct varchar(max)
Declare @Quantity varchar(max)
Declare @UnitCost varchar(max)
Declare @PercentSplit varchar(max)
Declare @ExtendedCost varchar(max)

Declare @LastRQ varchar(9)
Declare @Seed varchar(20)

Declare @RowNum int
Declare @ColNum int

--If Len(@PONumber)<>9 Set @ErrMsg='PO Number must be a length of 9'

Set @CR=0
Set @LCR=0
Set @RowNum=0
While @CR<Len(@POLines) and @ErrMsg is null
Begin
	Set @CR=CharIndex('~@row@~',@POLines,@LCR)
	If @CR=0 Set @CR=Len(@POLines)+1
	Set @Row=SubString(@POLines,@LCR,@CR-@LCR)
	--Print '*'+@Row+'*'

	Set @CC=0
	Set @LCC=0
	Set @ColNum=0
	While @CC<Len(@Row)
	Begin
		Set @CC=CharIndex('~@col@~',@Row,@LCC)
		If @CC=0 Set @CC=Len(@Row)+1
		Set @Col=SubString(@Row,@LCC,@CC-@LCC)
		If @ColNum=0 Set @POLine=@Col
		If @ColNum=1 Set @PODesc=@Col
		If @ColNum=2 Set @Acct=@Col
		If @ColNum=3 Set @Quantity=@Col
		If @ColNum=4 Set @UnitCost=@Col
		If @ColNum=5 Set @PercentSplit=@Col
		If @ColNum=6 Set @ExtendedCost=@Col
		Set @ColNum=@ColNum+1
		--Print '*'+@Col+'*'

		Set @LCC=@CC+7
	End

	Set @Quantity=Replace(@Quantity,',','')
	Set @UnitCost=Replace(@UnitCost,',','')
	Set @PercentSplit=Replace(@PercentSplit,',','')
	Set @ExtendedCost=Replace(@ExtendedCost,',','')

	If Not(/*IsNull(@POLine,'')='' and */IsNull(@PODesc,'')='' and IsNull(@Acct,'')='' and IsNull(@Quantity,'')=''
			and IsNull(@UnitCost,'')='' and IsNull(@PercentSplit,'')='')
 	Begin
		If Right(@Acct,1)=')' Set @Acct=RTrim(SubString(@Acct,1,Len(@Acct)-CharIndex('(',Reverse(@Acct))))

		If Len(@POLine)<1 Set @POLine='0'+@POLine
		If Len(@POLine)<>2 Set @ErrMsg='PO Line must be a length of 2'
		If Len(@PODesc)>255 Set @ErrMsg='Description cannot exceed a length of 255'
		If Len(@Acct)>30 Set @ErrMsg='Account must be a length of 30'

		If IsNull(@UnitCost,'')<>'' and @ErrMsg is null
			If IsNumeric(@UnitCost)<>1 Set @ErrMsg='Unit Cost must be a number'

		If IsNull(@PercentSplit,'')<>'' and @ErrMsg is null
			If IsNumeric(@PercentSplit)<>1 Set @ErrMsg='Percent Split must be a number'

		If @POLine>='A' and @ErrMsg is null
		Begin
			If IsNumeric(@Quantity)=1
			Begin
				If Cast(@Quantity as money)=0 Set @Quantity=''
			End
			If IsNull(@Quantity,'')<>'' Set @ErrMsg='Quantity must be blank for a split line'
			If Cast(@UnitCost as money)=0 Set @UnitCost=''
			If Cast(@PercentSplit as money)=0 Set @PercentSplit=''
			If @ErrMsg is null			
				If IsNull(@UnitCost,'')<>'' and IsNull(@PercentSplit,'')<>'' Set @ErrMsg='Fill in ONLY the Unit Cost OR Percentage on line '+@POLine
			If @ErrMsg is null			
				If IsNull(@UnitCost,'')='' and IsNull(@PercentSplit,'')='' Set @ErrMsg='Must fill in either the Unit Cost OR Percentage on line '+@POLine
			
		End
		Else
		Begin
			If IsNumeric(@Quantity)<>1 Set @ErrMsg='Quantity must be a number'
			If IsNumeric(@UnitCost)<>1 Set @ErrMsg='Unit Cost must be a number'
		End

		If IsNull(@ExtendedCost,'')<>'' and @ErrMsg is null
			If IsNumeric(@ExtendedCost)<>1 Set @ErrMsg='Extended Cost must be a number'

		If @ErrMsg is null
			Insert into @POLinesWork 
					Select @RowNum,@POLine,@PODesc,@Acct
						,@Quantity
						,@UnitCost
						,@PercentSplit
						,@ExtendedCost
						,null,null
						,'',0,0,'','','','','','','','','','','','','',0,0,0
		--Insert into @POLinesWork values (@RowNum,@POLine,@PODesc,@Acct,@Quantity,@UnitCost,@PercentSplit,@ExtendedCost
		--				,'',0,0,'','','','','','','','','','','','','',0,0,0)

		Set @RowNum=@RowNum+1
	End

	Set @LCR=@CR+7
End

Update @POLinesWork Set SLASN=SL.SLASN
	From @POLinesWork PO
	Left Join SLASN_T SL on SL.SLASNDesc=PO.Acct

If Exists(Select * From @POLinesWork Where SLASN is null)
	Set @ErrMsg='Account Not Found: '+(Select Min(Acct) From @POLinesWork Where SLASN is null)