March 25, 2008 at 5:13 am
I have a cursor with a nested cursor.
The outer cursor gathers data then inserts the ID number and a predefined status into a table for each loop i.e. (789377,'Collecting Data'). The nested cursor gathers more data for each item from the outer cursor. If the nested cursor gathers data and that data is deemed invalid by me (maybe some sub-data is missing so I don't want to include that item in the report) I want to update the table that was first inserted into with a new status for that paticular item. i.e. (789377,'Bad Data').
I can insert the data for each intial cursor loop but if I try to update one of those records from within the nested cursor it doesn't work.
Is there a way to update a row that you just inserted within the same cursor?
In the nested cursor I specified a waittime of 20 seconds before my update statement. I ran the cursor and in another query window I selected from that table and did the same update that should take place in the nested cursor. I got 1 row effected in the result pain for each statement. So the update statement didn't fail but the changes weren't commited either.
Any thoughts?
March 25, 2008 at 5:32 am
Could you post the code please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 25, 2008 at 5:40 am
It's a lot of code. You really don't want to go thru all of it. I'll post code that essential does the same thing.
March 25, 2008 at 5:55 am
Okay here is the gist of it. In the nested cursor I am updating the same table that the main cursor inserted into. This happens if there is bad data for some reason.
Declare MainCursor Cursor For
select ID from Table where ID in (1234,5555,7890)
Open MainCursor
Fetch Next From MainCusor Into @ID
While @@Fetch_Status = 0
BEGIN
insert into t_CusorTable (ID,Status) Values (@Id,'Gathering Data')
Set @Body = @ID + ',^'
Declare NestedCursor Cursor for
select Tran_ID from t_table2 where ID = @Id
Open NestedCursor
Fetch Next From NestedCursor Into @TranId
While @@Fetch_Status = 0
BEGIN
exec s_GetDetailsForNestedCusorID @Tran_ID,@Quantity out,@Amount out
IF @Quantity < 1 or isnull(@Amount,0) = 0
BEGIN
update t_CursorTable set Status = 'Bad Data' where ID = @ID
Close NestedCursor
Deallocate NestedCursor
Close MainCursor
Deallocate MainCursor
GoTo Error
END
--Build Body with each loop--
SET @Body = @Body + @Body
END
Fetch Next From NestedCursor Into @TranId
Close NestedCursor
Deallocate NestedCursor
FetchNext From MainCursor Into @Id
Close MainCursor
Deallocate MainCursor
March 25, 2008 at 6:04 am
Can't see anything offhand that would make the update fal. It's not as if you're updating a row that the cursor's based off.
I'm not sure a cursor's necessary at all here. What does that GetDetails... proc do?
p.s. That's partially why I asked for the code, to see if a rewrite sans cursor would be possible
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 25, 2008 at 6:17 am
Both Cursors call data from different tables than the inserted/updated table.
For some reason it won't update the row/s that were inserted within that currrent cursor. If I write a generic update statment and call it in place of the other one it'll update all data in that table except data that was inserted during that cursor. Maybe I need to go with a different type of cursor.
March 25, 2008 at 6:31 am
What this proc really does is get details for various amounts of orders (Could be 1 or 1000) and build a @Body(varchar) so I can write it to a txt file. The txt file has to be in a specific format i.e.
"Header Record",^
"01","210","4","20080325","0815","004010"^
"HR","00","28976","130482A","20060310","1219.80","20060303","","","USD,^
"LI","1",""CHARGE","","","","","119.80","","","","",^
"HR","00","28007","","USD,^
"LI","1","Main CHARGE","","","","","725.00","","","","",^
"LI","2","Secondary CHARGE","","","","","140.76","","","","",^
"Trailing Data",^
The HR record is data that is built from the MainCursor and then the LI records (LineItems) are built from the nested cursor. Each has to be on it's own line with the LineItems incrementing by one. The main @Body is built then l add Header and Trailing Data and then create a TXT File.
If any data is wrong or missing I can't include it so as I loop thru the cursor I perform various checks while building @Body, inserting into log tables which orders are actually being used and which are "failing". If they fail I remove that portion from @Body and log the failure in another table.
The initial insert logs all the orders that were called. If one fails we insert it into a Log table with a reason for failure but then I wanted to update that initial insert table with a status of failed.
Do you think this is something that could be done without a Cursor?
March 25, 2008 at 6:31 am
I'm with Gail...not sure a cursor (or nested cursor...yuk) is necessary, but seeing what you are doing would help us determine that.
Have you investigated using 'UPDATE...FROM'...this allows you to select data from other tables and insert it into a 'target' table.
If it was easy, everybody would be doing it!;)
March 25, 2008 at 6:44 am
Okay you asked for it!!!! 🙂
March 25, 2008 at 6:54 am
Okay I trimmed the proc down and included only the cursor portion so it was a little less lengthy.
--Get BatchControl Number
BEGIN
INSERT INTO t_210_BatchControl (Customer,BatchStatus) VALUES (@CustomerName,'Gathering Invoices')
SET @ControlNumber = Scope_Identity()
IF isnull(@ControlNumber,0) = 0 BEGIN Print 'ControlNumber 0 or null.' SET @Error = 1 Set @ErrorMSG = 'Failure' Goto Error END
END
Declare EDI Cursor For
select Number from t_RDER where number in (28296,30298,28807) /* 24420 */
OPEN EDI
FETCH NEXT FROM EDI INTO @OrderNumber
IF @@Fetch_Status = 0
BEGIN
SET @InvoiceCount = 1
END
WHILE @@Fetch_Status = 0
BEGIN
SET @LineItemNumber = 1
--Get Details
Exec dbo.s_EDI_210_HeaderDetail @OrderNumber,@OrderID out,@RecordID out,@PurposeCode out,@InvoiceNumber out,@BOLNumber out,@BillingDate out,@NetAmountDue out,@PONumber out,@SCAC out,@ShipperName out,@ShipperAddress out,@ShipperCity out,@ShipperState out,@ShipperZip out,@ShipperID out,
@ConsigneeName out,@ConsigneeAddress out,@ConsigneeCity out,@ConsigneeState out,@ConsigneeZip out,@ConsigneeID out,@BillToName out,@BillToAddress out,@BillToCity out,@BillToState out,@BillToZip out,@BillToID out,@TotalGrossWeight out,@TotalNetWeight out,@Charge out,@TotalLadingQuantity out,@ShipPay out,
@EDIRef out,@AccountCode out,@PickupDate out,@DeliveryDate out,@HeaderDetailReturnCode out
--=============== Establish Body ==========================
SET @MainBody =
'"HR"' +','+ --Recode ID
convert(varchar(20),@OrderNumber) +'","'+ --Invoice Number
@BOLNumber +'","'+ -- BOL Number
@BillingDate +'","'+ --Billing Date
convert(varchar(20),@NetAmountDue) +'","'+ --Net Amount Due
@DeliveryDate +'","'+--Delivery Date
@PoNumber +'","'+-- Purchase Order #
@ShipperName +'","'+--ShipperName
@ShipperAddress +'","'+ --Shipper Address
@ShipperCity +'","'+ --Shipper City
@ShipperState +'","'+ --Shipper State
@ShipperZip +'","'+ --Shipper ZIP
convert(varchar(20),@ShipperID) +'","'+ --Shipper ID
@ConsigneeName +'","'+ --Consignee Name
@ConsigneeAddress +'","'+ --ConsigneeAddress
@ConsigneeCity +'","'+ --Consignee City
@ConsigneeState +'","'+ --Consignee State
@ConsigneeZip +'","'+ --Consignee Zip
convert(varchar(20),@ConsigneeID) +'","'+ --Consignee ID
@BillToName +'","'+ --BillTo Name
@BillToAddress +'","'+ --BillTo Address
@BillToCity +'","'+ --BillTo City
@BillToState +'","'+ --BillTo State
@BillToZip +'","'+ --BillTo Zip
convert(varchar(20),@BillToID) +'","'+ --BillTo ID
convert(varchar(20),@TotalGrossWeight)+'","'+ --Total Gross Weight
'","'+--Total Net Weight
convert(varchar(20),@NetAmountDue) +'","'+--Charge
'USD,'+--Ctry (Country) Currency Code
'^' +Char(13) +char(10)--Record Terminator
---Invalid Header Detail Error Trap
IF @HeaderDetailReturnCode = 1
BEGIN
exec dbo.s_EDI_210_InvoiceErrorInsert @OrderNumber,@MainBody,@ControlNumber,'Invalid Header Detail',@ReturnCode out
IF @ReturnCode > 0 BEGIN PRINT 'Header Detail has bad data. Error Insert successful' END
IF isnull(@ReturnCode ,0) = 0
BEGIN
PRINT 'Invoice Error Insert Failed Terminating Batch'
Set @Error = 3
SET @ErrorMSG = 'Invoice Error Insert Failed'
CLOSE EDI
DEALLOCATE EDI
Goto Error
END
SET @InvoiceCount = @InvoiceCount- 1
GOTO NextOrder
END
--Insert each invoice into table if Valid
exec dbo.s_EDI_210_InvoiceInsert @ControlNumber,@OrderNumber,'Gathering Invoices',@ReturnCode out
IF @ReturnCode > 0 BEGIN PRINT 'Insert Successfull' END
IF isnull(@ReturnCode,0) = 0
BEGIN
PRINT 'Invoice Insert Failed Terminating Batch'
Set @Error = 2
SET @ErrorMSG = 'Invoice Insert Failed'
CLOSE EDI
DEALLOCATE EDI
Goto Error
END
--IF @Body has data append to it othewise set it to the mainbody only
IF @Body > '' BEGIN SET @Body = @Body + char(13)+Char(10) +@MainBody END
IF isnull(@Body,'') = '' BEGIN SET @Body = @MainBody END
--Set Each Invoice Body Individually so we can remove it later if there is a problem
SET @InvoiceBody = @MainBody
--============ Line Item Cursor and Details ===============
Declare EDID Cursor for
select a.ID
from t_Transaction_Detail a
JOIN t_Transaction b on a.Transaction_Id = b.ID
left outer join (select ID,ENG_ITEMDESC from t_XREF) Fee on Fee.ID = a.ITEMTYPE_ID
where a.order_id =@OrderID
OPEN EDID
FETCH NEXT FROM EDID INTO @LineItemID
WHILE @@Fetch_Status = 0
BEGIN
--Get Line Item Details
select @LineItemDesc=Fee.Eng_Itemdesc,@LineItemCharge =Rate*Quantity
from t_Transaction_Detail a
JOIN t_Transaction b on a.Transaction_Id = b.ID
left outer join (select ID,ENG_ITEMDESC from t_XREF) Fee on Fee.ID = a.ITEMTYPE_ID
where a.id =@LineItemID
--Esablish Line Item Body
SET CONCAT_NULL_YIELDS_NULL OFF
SET @LineItemBody = '"LI' +'","'+ --Record ID
convert(varchar(20),@LineItemNumber)+'","'+ --Line Item Number
@LineItemDesc+'","'+ --Line Item Desc
convert(varchar(20),@LineItemCharge) +--Charge
'^' --Record Terminator
--IF Line Item Data is invalid Replace and Log
IF isnull(@LineItemBody,'') = '' or isnull(@LineItemDesc,'') = '' or convert(varchar(20),isnull(@LineItemCharge,'')) =''
BEGIN
exec dbo.s_EDI_210_InvoiceErrorInsert @OrderNumber,@LineItemBody,@ControlNumber,'Invalid Line Item Detail',@ReturnCode out
IF @ReturnCode > 0 BEGIN PRINT 'Line Item has bad data. Error Insert successful' END
IF isnull(@ReturnCode ,0) = 0
BEGIN
PRINT 'Invoice Error Insert Failed Terminating Batch'
Set @Error = 3
SET @ErrorMSG = 'Invoice Error Insert Failed'
Close EDID
Deallocate EDID
CLOSE EDI
DEALLOCATE EDI
Goto Error
END
--Remove Bad Invoice Body and decrement Invoice Count
SET @Body = replace(@Body,@InvoiceBody,'')
SET @InvoiceCount = @InvoiceCount - 1
END
--Set Main Body
SET @BODY = @Body + @LineItemBody
--Set Individual Invoice Body so we can remove if any problems
SET @InvoiceBody = @InvoiceBody + @LineItemBody
--Fetch Next Record,Append to @Body and Increment LineItemNumber
FETCH NEXT FROM EDID INTO @LineItemID
IF @@Fetch_Status = 0
BEGIN
SET @Body = @Body +Char(13)+char(10)
SET @InvoiceBody = @InvoiceBody + Char(13) + Char(10)
SET @LineItemNumber =@LineItemNumber + 1
END
END
IF isnull(@LineItembody,'') = '' --Line Item Body will be null if an Order Number doesn't have any rows in the transaction tables thus returning no L1 data.
BEGIN
--Update Statment that doesn't work goes here
exec dbo.s_EDI_210_InvoiceErrorInsert @OrderNumber,'Line Item Null or Blank',@ControlNumber,'Invalid Line Item Detail',@ReturnCode out
IF @ReturnCode > 0 BEGIN PRINT 'Line Item has bad data. Error Insert successful' END
IF isnull(@ReturnCode ,0) = 0
BEGIN
PRINT 'Invoice Error Insert Failed Terminating Batch'
Set @Error = 3
SET @ErrorMSG = 'Invoice Error Insert Failed'
Close EDID
Deallocate EDID
CLOSE EDI
DEALLOCATE EDI
Goto Error
END
--Remove Bad Invoice Body and decrement Invoice Count
SET @Body = replace(@Body,@InvoiceBody,'')
SET @InvoiceCount = @InvoiceCount - 1
END
Close EDID
Deallocate EDID
NextOrder:
FETCH Next From EDI Into @OrderNumber
--Reset LineItemNumber once Next Shipment is fetched
SET @LineItemNumber = 0
IF @@Fetch_Status = 0
--Increment Invoice Count only if another record is retrieved
BEGIN SET @InvoiceCount = @InvoiceCount + 1 END
END
CLOSE EDI
DEALLOCATE EDI
March 25, 2008 at 8:24 am
Because you are building a output string for EDI, it doesn't appear that you will be able to totally get away from some kind of row by row processing, but you can improve upon what you have (we can go there later).
Just looking at this, it looks like it will be a performance problem (that will depend on the volume of data, etc.), but you need to see if that is indeed the case before undertaking the effort to rewrite it.
Going back to your original problem of the updates not working, have you tried running SQL Profiler to see what is going on?
If it was easy, everybody would be doing it!;)
March 25, 2008 at 8:46 am
Trader Sam (3/25/2008)
Because you are building a output string for EDI, it doesn't appear that you will be able to totally get away from some kind of row by row processing, but you can improve upon what you have (we can go there later).
Why not tackle this with some kind of UNION query, or multiple inserts to a temp table. I've yet to see anything dictating a cursor here.
As of right now I'm seeing something that could be achieved with 3 inserts and some interesting sorting. Maybe a temp table to store the intermediate results, or XML, since the record layouts change. Would handle the data volume problem and the resulting performance issue with no problem.
Of course - we would need the ENTIRE spec for what the file would have to look like, but still
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 25, 2008 at 9:10 am
I have done this in the past using temp tables, but I cheated and used while loops, but only to build the final output string. It was the most obvious solution to me at the time and the data volume was low.
I would be interested to see the solution using UNIONS as that would eliminate the RBAR (row by row) which would be beneficial in larger data sets.
If it was easy, everybody would be doing it!;)
March 25, 2008 at 10:53 am
#1.Well - I'd probably build a large FOR XML EXPLICIT query, spit it to disk and post-process it to the format they want (way faster than solution #2). Will create a XML big file, but with the right indexing would kill the cursor AND #2, IMO.
#2. Given 2000, if you wish to avoid XML, there is some amount of un-prettiness, since you have to build the actual output by row into a fairly large VARCHAR field. Which admittedly will cause table scans (a form of RBAR), but still - much better over all than cursor behavior.
That being said - if you were to create a temp table looking something like this:
Create table #MyOutput (InvoiceNumber varchar(20),
LineMainRank int,
LineSubRank int,
LineNumber int,
rowOutput varchar(1000))
And then simply insert all of the various TYPES of rows together, you should get a LOT better than the cursor.
Example (air-code warning, since we have NO DDL to speak of). I am NOT going to decipher or infer what the various SP's are doing.
[Code]
Insert #myOutPut( InvoiceNumber,LineMainRank , LineSubRank, LineNumber, rowOutput)
select '',0,0,Null,'"Header Record"' UNION ALL
--I'm reading this to be static, if it's not, substitute for what builds it
select '',1,0,Null,'"01","210","4","20080325","0815","004010"'
--insert the Main Invoice number rows
Insert #myOutPut( InvoiceNumber,LineMainRank , LineSubRank, LineNumber, rowOutput)
select invnumb, 2,0,null,'"HR","'+convert(varchar(20),OrderNumber) +'"' --etc - build the line
From T_orders
where type='MainInvoice' --replace with your logic
--insert the Main Charge Lines here...
Insert #myOutPut( InvoiceNumber,LineMainRank , LineSubRank, LineNumber, rowOutput)
select invnumb, 3,1,null,'"LI","1","Main CHARGE","'+convert(varchar(20),OrderNumber) +'"' --etc - build the line
From T_orders
where type='MainChargeSub' --replace with your logic
--insert the Secondary Charge Lines here...
Insert #myOutPut( InvoiceNumber,LineMainRank , LineSubRank, LineNumber, rowOutput)
select invnumb, 3,1,null,'"LI","2","Secondary CHARGE","'+convert(varchar(20),OrderNumber) +'"' --etc - build the line
From T_orders
where type='2ndChargeSub' --replace with your logic
--etc...
[/code]
Again - very rough, since we don't have specs to go on. Still - you end up with 3-4 inserts based on tables. And this will only take a fraction of the time a cursor might take to build the same thing.
If you need to build the line numbers for some reason- look up Jeff's Running totals article with how to update a temp table like this with line numbers (note the field is already there).
Your output is then simply
select RowOutput
from #myOutput
order by InvoiceNumber,LineMainRank , LineSubRank, LineNumber
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 25, 2008 at 11:25 am
Ahhh...the key to your solution, Matt, is to build the row string on the INSERT into the temp table, rather than building it when SELECTING from the temp tables.
It is amazing how a concept as simple as that can make coding more difficult if you don't catch it.
If it was easy, everybody would be doing it!;)
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply