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

I have two tables i need to insert data into using a stored procedure. One is main table and other is its detail table.There would only be single row in main table
The problem is the count of rows to insert into the detail can vary. Sometimes there won't be any and in other occasions there would be like 10. I have to include the insert into both tables in a single SP. How can i achieve this? Should I pass the total rows as a single string and its count as an int variable and decode it in sp?
Help T.Y. (I am using vb6)

Well to insert into two table you just use two insert statements. The number of rows inserted doesn't change that. I'm not clear on what your problem is.

How does your sproc know what rows to create in the detail table? Where's it getting the data it needs to insert from? Could you maybe provide an example?

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

Code:

---

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)

---

We accomplish this by passing in the child records as an XML string, which then gets parsed out and inserted into the appropriate table(s).

-tg

Do you get some kind of native SQL XML-handling?

Quote:

---

Originally Posted by FunkyDexter

Well to insert into two table you just use two insert statements. .... Could you maybe provide an example?

---

Its like a stored procedure which updates details about a person. That person may have/not have children. Also their count will differ. After supplying parameters like age,sex etc you have to update children's name in the details table 'children details'. Maybe one, two, or three records. I cannot create 'n' number of parameters for that. Looks like I have to think about xml handling like they said..

OK, got it. In that case either SzLamany's suggestion is the sort of thing I've done in the past. You basically concatenate the child rows together with known dividers (he's using @row@ and @col@) and pass those into the sproc as one long varchar that you then loop through in the sproc to pull the individual parms out. I like TG's suggeston of using xml though. It's basically the same technique if you think about it (because xml is ultimately just a string) except that SQL Server has got some native xml handling (I haven't played with it much yet) which probably makes your life easier.

Personally, though, I usually prefer to break things like this into two sprocs, one for the parent and one for the child. It does increase network traffic (because you'll make lots of calls to the child sproc) but I think it improves maintainability. Whether that's the right choice really depends on whether performance is more important to you that maintainability but I find the network isn't the bottleneck it was ten years ago and maintanability usually wins out for me.

I use the technique I mention from an AJAX POST from a web page - into a web method - that's calling the UPDATE sproc. That web method is blind to the parameters - they self bind in the JavaScript before it ever gets POST'd to the web method.

The JavaScript is reading my own home grown repeater-panel-thingy and building that @row@ and @col@ grid - passing that in a JSON string to the web method.

Since I can only bind parameters I had to break it up in the SPROC - which I didn't mind anyway because that is where I am allowed to pass back validation messages - which you will see in that sproc.

Working nicely for me - my own mini asp.net...

Here's an example of how we turn data into XML:

Code:

---

    SELECT [ZIPCITYSTATEID]
                ,[SITEID]
                ,[STATE]
                ,[CITY]
                ,[ZIPCODE]
          ,[ISSELECTED]       
        FROM dbo.ZIPCITYSTATE
        for xml raw('ITEM'),type,elements,root('SITES'),BINARY BASE64

---

And here's an example of how we the shred that XML back into data:

Code:

---

SELECT

T.c.value('(ZIPCITYSTATEID)[1]','uniqueidentifier') AS 'ZIPCITYSTATEID',
T.c.value('(SITEID)[1]','uniqueidentifier') AS 'SITEID',
T.c.value('(STATE)[1]','nvarchar(200)') AS 'STATE',
T.c.value('(CITY)[1]','nvarchar(200)') AS 'CITY',
T.c.value('(ZIPCODE)[1]','nvarchar(100)') AS 'ZIPCODE',
T.c.value('(ISSELECTED)[1]','bit') AS 'ISSELECTED'
FROM @ITEMLISTXML.nodes('/SITES/ITEM') T(c)

---

Tyipcally we wrap these up into functions, where the first one is a scalar that returns XML type, and the second is a table function... we can then pass the XML back and forth between the database and the client and not care about how many there are... when the client code returns the XML, we simply send it to the shred function and upsert the results.


-tg

@tg - thanks - I might look into switching to XML for that @POLINES parameter - from my web app.

Not that it is a problem in your case since I know you use SQL Server, and usually keep up with more recent version... but for anyone else looking to use that - it's valid for SQL Server, starting SQLServer 2010 ... prior to that using the OPEN XML clause was needed for the shredding... I can't speak to other DBMSs though... they may have their own methods.

-tg

Quote:

---

Originally Posted by techgnome

but for anyone else looking to use that - it's valid for SQL Server, starting SQLServer 2010 ...

---

Actually, starting with SQL Server 2005