Results 1 to 12 of 12

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

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    66

    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)

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

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

    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?
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

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

    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)
    Last edited by szlamany; Oct 16th, 2013 at 04:36 AM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

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

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

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

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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    66

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

    Quote Originally Posted by FunkyDexter View Post
    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..

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

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

    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.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

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

    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...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

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

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

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

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

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

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

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  12. #12
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

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

    Quote Originally Posted by techgnome View Post
    but for anyone else looking to use that - it's valid for SQL Server, starting SQLServer 2010 ...
    Actually, starting with SQL Server 2005

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width