-
Oct 15th, 2013, 07:47 AM
#1
Thread Starter
Lively Member
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)
-
Oct 16th, 2013, 03:45 AM
#2
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
-
Oct 16th, 2013, 04:33 AM
#3
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.
-
Oct 16th, 2013, 11:01 AM
#4
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
-
Oct 16th, 2013, 05:01 PM
#5
Re: Update 2 tables in a procedure. Row count of one table unknown
Do you get some kind of native SQL XML-handling?
-
Oct 17th, 2013, 12:24 AM
#6
Thread Starter
Lively Member
Re: Update 2 tables in a procedure. Row count of one table unknown
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..
-
Oct 17th, 2013, 04:19 AM
#7
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
-
Oct 17th, 2013, 05:12 AM
#8
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...
-
Oct 17th, 2013, 07:01 AM
#9
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
-
Oct 17th, 2013, 08:38 AM
#10
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.
-
Oct 17th, 2013, 09:08 AM
#11
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
-
Oct 21st, 2013, 09:01 AM
#12
Re: Update 2 tables in a procedure. Row count of one table unknown
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
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|