September 11, 2008 at 9:23 am
Hi, thanks in advance for any help and apologies if this should be in the newbie section :D.
Unfortunately i'm forced to used nested cursors (i'm trying to replicate a stock allocation routine).
Therefore if i'm in a nested cursor is the @@Fetch_Status relative to that cursor or do i need to specify the cursor it applies to using syntax ?
Hope that makes sense.
September 11, 2008 at 9:37 am
I believe it shows the result of the last fetch operation that occurred.
Are you sure nested cursors are required? Want to post the gode here and see if any of the T-SQL wizards can rewrite it for you?
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
September 11, 2008 at 10:35 am
As Gail stated, it represents the last statement run. Often with nested cursors you want to set the value to a local variable so that it doesn't get over written unexpectedly by your next fetch statement. Especially if you are using the fetch status to control a while loop.
Gary Johnson
Sr Database Engineer
September 11, 2008 at 10:41 am
And as Gail also asked, could you post your code? It is quite possible that someone on this site could help you rewrite your current code without using cursors or while loops.
😎
September 11, 2008 at 2:11 pm
Hi, thanks for the replies so far... I'm now at home so my code isn't at hand but logically it's still in development and hence incomplete.
Basically i'm looping through future stock supply (by region) and current cutomer orders (also by region) and calculating if the current future supply can satisfy that order (running total in effect). Based on this i update a table variable for the order level data which then updates a master table which is then used by the overall system. Effectively it is a numbers comparison, with no real join architecture between order and supply so it's calculate and update based on priority per order (on a per order basis) so i'm struggling to see how it could be done a better way (i'm fairly new to SQL Server but am no stranger to relational database design).
Anyways, i'll Hopefully have the logic completed by mid next week (using cursors) and will post it for the eager eyes out there to play with. I assume by most peoples comments that cursors are to be avoided ? I did try to code using variable tables/row ids and WHILE loops, however as far as i could tell this would result in much more bespoke code to inevitably replicate the functionality of cursors; thought and views people ?
September 11, 2008 at 2:37 pm
Finish the logic, we'll kock it out for you ;).
September 11, 2008 at 2:54 pm
Cursors can and should be avoided most of the time. Very few actual uses. Substituting a While loop for a cursor isn't really a solution, it just changes the name (and usually runs even worse).
It's possible you need a cursor. More likely a running totals calculation with the right indexes will do what you need. We'll have to see the code you come up with. Then we might be able to improve it.
As to the question about which forum to post it, it doesn't matter that much. Pick what seems most appropriate to you. The main thing to avoid is posting SQL 2000 questions in the 2005 forums, because the answers may not work if people think they can use 2005-only features and the database won't run them. (Same applies to the 2008 forums, of course.) Other than that, the main rule is don't post the same question in multiple forums.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 15, 2008 at 10:03 am
Please find my code as below... please feel free to highlight any errors and any alternative approaches that could be taken.
ALTER PROCEDURE [dbo].[sp_BPMUpdateStkDlyOrd]
@QTY smallint = NULL
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION
--OUTER MOST LOOP, CYCLE THROUGH STOCK DELAY COMPS
DECLARE cStkDelay CURSOR READ_ONLY FAST_FORWARD
FOR SELECT tbl_BPMStockDelays.Component, tbl_BPMStockDelays.WHID, tbl_BPMStockDelays.RecoveryDate
FROM tbl_BPMStockDelays INNER JOIN
tbl_BPMStockDelayOrders ON tbl_BPMStockDelays.Component = tbl_BPMStockDelayOrders.Component AND
tbl_BPMStockDelays.WHID = tbl_BPMStockDelayOrders.WHID
GROUP BY tbl_BPMStockDelays.Component, tbl_BPMStockDelays.WHID, tbl_BPMStockDelays.RecoveryDate
OPEN cStkDelay
DECLARE @dComp int, @dWHID smallint, @dRecDate smalldatetime
FETCH NEXT FROM cStkDelay INTO @dComp, @dWHID, @dRecDate
WHILE @@FETCH_STATUS = 0
BEGIN
--MIDDLE LOOP, CYCLE THROUGH FUTURE SUPPLY
DECLARE cFutSupp CURSOR READ_ONLY FAST_FORWARD
FOR SELECT SupplyDate, Component, WHID, ComponentQTY
FROM tbl_BPMFutureSupply
WHERE Component = @dComp AND WHID = @dWHID
OPEN cFutSupp
DECLARE @sSuppDate smalldatetime, @sComp int, @sWHID smallint, @sCompQTY smallint
FETCH NEXT FROM cFutSupp INTO @sSuppDate, @sComp, @sWHID, @sCompQTY
SELECT @QTY = @sCompQTY
WHILE @@FETCH_STATUS = 0
BEGIN
--INNER MOST LOOP, CYCLE THROUGH COMP ORDERS
DECLARE cCompOrders CURSOR
FOR SELECT tbl_BPMStockDelayOrders.OrderNum, tbl_BPMStockDelayOrders.Component, tbl_BPMStockDelayOrders.ComponentQTY,
tbl_BPMStockDelayOrders.WHID, tbl_BPMStockDelayOrders.ProcessDate, tbl_BPMStockDelayOrders.Allocable,
tbl_BPMStockDelayOrders.RecoveryDate
FROM tbl_BPMStockDelayOrders INNER JOIN
tbl_BPMHDR ON tbl_BPMStockDelayOrders.OrderNum = tbl_BPMHDR.OrderNum
WHERE (tbl_BPMStockDelayOrders.Component = @dComp) AND (tbl_BPMStockDelayOrders.WHID = @dWHID) AND
(tbl_BPMStockDelayOrders.ProcessDate IS NULL) AND (tbl_BPMHDR.Closed IS NULL) OR
(tbl_BPMStockDelayOrders.ProcessDate <> dbo.BDate(GETDATE()))
ORDER BY tbl_BPMHDR.LatDelDate
FOR UPDATE OF tbl_BPMStockDelayOrders.ProcessDate, tbl_BPMStockDelayOrders.Allocable,
tbl_BPMStockDelayOrders.RecoveryDate
OPEN cCompOrders
DECLARE @oOrdNum varchar(40), @oComp int, @oCompQTY smallint, @oWHID smallint, @ProcDate smalldatetime, @oAlloc bit, @oRecDate smalldatetime
FETCH NEXT FROM cCompOrders INTO @oOrdNum, @oComp, @oCompQTY, @oWHID, @ProcDate, @oAlloc, @oRecDate
WHILE @@FETCH_STATUS = 0
BEGIN
IF @QTY >= @oCompQTY
BEGIN
UPDATE tbl_BPMStockDelayOrders
SET Allocable = 1, ProcessDate = dbo.BDATE(GETDATE())
WHERE OrderNum = @oOrdNum AND Component = @oComp AND WHID = @oWHID
SELECT @QTY = @QTY - @oCompQTY
END
ELSE
IF @QTY <= 0
BEGIN
BREAK
END
FETCH NEXT FROM cCompOrders INTO @oOrdNum, @oComp, @oCompQTY, @oWHID, @ProcDate, @oAlloc, @oRecDate
END
CLOSE cCompOrders
DEALLOCATE cCompOrders
FETCH NEXT FROM cFutSupp INTO @sSuppDate, @sComp, @sWHID, @sCompQTY
SELECT @QTY = @sCompQTY
END
CLOSE cFutSupp
DEALLOCATE cFutSupp
FETCH NEXT FROM cStkDelay INTO @dComp, @dWHID, @dRecDate
END
CLOSE cStkDelay
DEALLOCATE cStkDelay
END
COMMIT
September 15, 2008 at 11:12 am
I think I can rewrite that into a set-based solution. To be sure, I need table definitions and some sample data for all the tables in the query.
That seems to be:
tbl_BPMStockDelays
tbl_BPMStockDelayOrders
tbl_BPMFutureSupply
tbl_BPMHDR
I'm also not certain the final OR statement in your innermost query is correctly written. What is that supposed to do? And, what does the BDate function do?
If you provide those, I'm pretty sure I can improve this for you pretty easily.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 15, 2008 at 12:50 pm
The table tbl_BPMHDR is really needed as it is only used for sorting by a field in that table :
tbl_BPMStockDelays :
CREATE TABLE [dbo].[tbl_BPMStockDelays](
[TimeStamp] [timestamp] NOT NULL,
[ExtractDate] [smalldatetime] NOT NULL CONSTRAINT [DF_Table_1_RecoveryDate1] DEFAULT (getdate()),
[Component] [int] NOT NULL,
[WHID] [smallint] NOT NULL,
[RecoveryDate] [smalldatetime] NULL,
[CutOffDate] [bit] NOT NULL CONSTRAINT [DF_tbl_BPMStockDelays_] DEFAULT ((0)),
CONSTRAINT [PK_tbl_StockDelays] PRIMARY KEY CLUSTERED
(
[Component] ASC,
[WHID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG01_Data_Objects]
) ON [FG01_Data_Objects]
15/09/2008 09:196482440600NULLFALSE
15/09/2008 09:196482440622NULLFALSE
15/09/2008 09:176674973622NULLFALSE
---------------------------------------------------------------------------
tbl_BPMStockDelayOrders : (haven't finalised the structure yet on this table)
CREATE TABLE [dbo].[tbl_BPMStockDelayOrders](
[OrderNum] [varchar](40) NOT NULL,
[Component] [int] NOT NULL,
[WHID] [smallint] NOT NULL,
[ComponentQTY] [smallint] NOT NULL,
[ComponentStatusID] [char](2) NOT NULL,
[ProcessDate] [smalldatetime] NULL,
[Allocable] [bit] NOT NULL,
[RecoveryDate] [smalldatetime] NULL
) ON [FG01_Data_Objects]
GO
SET ANSI_PADDING OFF
1234567864824406001UNULLFALSENULL
1234567964824406001UNULLFALSENULL
1234568066749736222U15/09/2008 00:00TRUENULL
1234568164824406001UNULLFALSENULL
1234568264824406221UNULLFALSENULL
1234568364824406001UNULLFALSENULL
1234568464824406221UNULLFALSENULL
1234568564824406001UNULLFALSENULL
1234568664824406001UNULLFALSENULL
1234568764824406001UNULLFALSENULL
1234568864824406221UNULLFALSENULL
1234568964824406001UNULLFALSENULL
1234569064824406221UNULLFALSENULL
1234569166749736222U15/09/2008 00:00TRUENULL
1234569264824406221UNULLFALSENULL
1234569364824406001UNULLFALSENULL
1234569464824406001UNULLFALSENULL
---------------------------------------------------------------------------
tbl_BPMFutureSupply:
CREATE TABLE [dbo].[tbl_BPMFutureSupply](
[TimeStamp] [timestamp] NOT NULL,
[ExtractDate] [smalldatetime] NOT NULL CONSTRAINT [DF_tbl_FutureStockSupply_ExtractDate] DEFAULT (getdate()),
[FutureSupplyID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_tbl_BPMFutureSupply_ID] DEFAULT (newid()),
[Component] [int] NOT NULL,
[WHID] [smallint] NOT NULL,
[SupplyID] [tinyint] NOT NULL,
[SupplyDate] [smalldatetime] NOT NULL CONSTRAINT [DF_Table_1_RecoveryDate] DEFAULT (getdate()),
[ComponentQTY] [smallint] NOT NULL,
CONSTRAINT [PK_tbl_BPMFutureSupply] PRIMARY KEY CLUSTERED
(
[FutureSupplyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [FG01_Data_Objects]
) ON [FG01_Data_Objects]
15/09/2008 09:19unique identifier6482440600130/09/2008 00:00 20
15/09/2008 09:18unique identifier6674973622112/09/2008 00:00 10
15/09/2008 09:19unique identifier6482440600123/09/2008 00:00 15
15/09/2008 09:17unique identifier6674973622130/09/2008 00:00 20
15/09/2008 09:18unique identifier6674973622115/10/2008 00:00 15
15/09/2008 09:19unique identifier6482440600121/10/2008 00:00 10
15/09/2008 09:19unique identifier6482440600118/09/2008 00:00 05
15/09/2008 09:17unique identifier6674973622119/09/2008 00:00 25
--------------------------------------------------------------------------------------
The logic on my inner most loop is 100% at the moment, the theory was getting to that level so i could perform some form of update. Also BDate() simply strips smalldatetime of the time value e.g. 2008/06/31 00:00:00. Thanks for any help at all or additional comments.
September 16, 2008 at 3:32 pm
In INNER_MOST_LOOP I believe your OR clause is going to cause you trouble. I don't think it is
going to do what you are thinking it is. I have removed it along with other items that weren't
being used at all. I haven't tried running this, but I think the basics of what you need are here.
Scott
--This table will contain all the data we care about
DECLARE @Temp TABLE @Temp (Component int, WHID smallint, ComponentQty smallint,
OrderNum varchar(40), Component int, OrderQty smallint,
WHID int, Allocable bit,
ActualQty smallint, DecreasingQty smallint)
INSERT INTO @Temp (Component, WHID, ComponentQty,
OrderNum, Component, OrderQty,
WHID)
SELECT sd.Component, sd.WHID, fs.ComponentQty,
sdo.OrderNum, sdo.Component, sdo.ComponentQTY,
sdo.WHID
FROM (
--OUTER MOST LOOP, CYCLE THROUGH STOCK DELAY COMPS
--I left this structured as it is since the grouping appears to provide you
-- with a unique component/WHID (I did remove RecoveryDate since it is never used)
SELECT sd.Component, sd.WHID
FROM tbl_BPMStockDelays sd
INNER JOIN tbl_BPMStockDelayOrders sdo ON sd.Component = sdo.Component
AND sd.WHID = sdo.WHID
GROUP BY sd.Component, sd.WHID) x
--MIDDLE LOOP, CYCLE THROUGH FUTURE SUPPLY
INNER JOIN tbl_BPMFutureSupply fs ON x.Component = fs.Component
AND x.WHID = fs.WHID
--INNER MOST LOOP, CYCLE THROUGH COMP ORDERS
INNER JOIN tbl_BPMStockDelayOrders sdo ON x.Component = sdo.Component
AND x.WHID = sdo.WHID
INNER JOIN tbl_BPMHDR d ON sdo.OrderNum = d.OrderNum
WHERE (d.Closed IS NULL)
AND ( (sdo.ProcessDate IS NULL)
--If you uncomment the OR, then I think it should be in these parenthese
--but I still think it is wrong, because including the OR will pick up the
--changed records that we did on previous days
--OR (sdo.ProcessDate <> dbo.BDate(GETDATE()) )
) --This annoying Smiley is supposed to be just a ")"
ORDER BY sd.Component, sd.WHID, d.LatDelDate
--Here is the start of the RUNNING TOTAL section. It will determine the quantity available for each
-- tbl_BPMStockDelayOrders record.
DECLARE @Component int
DECLARE @WHID smallint
DECLARE @NewItem int
DECLARE @ActualQty smallint
DECLARE @DecreasingQty smallint
SET @Component = 0
SET @WHID = 0
SET @NewItem = 0
SET @ActualQty = 0
SET @DecreasingQty = 0
UPDATE @Temp
SET @NewItem = CASE WHEN @Component = Component AND @WHID = WHID THEN 0 ELSE 1 END,
--This is the quantity available for this order, if we can't satisfy this order then skip it
@ActualQty = ActualQty = CASE WHEN @NewItem
THEN CASE WHEN ComponentQty >= OrderQty THEN OrderQty ELSE 0 END
ELSE CASE WHEN @DecreasingQty >= OrderQty THEN OrderQty ELSE 0 END END,
--Reduce the Total Quantiy by the available quantity
@DecreasingQty = DecreasingQty = CASE WHEN @NewItem
THEN ComponentQty - @ActualQty
ELSE @DecreasingQty - @ActualQty END,
@Component = Component,
@WHID = WHID
--Now we can update the data in one step
UPDATE sdo
SET Allocable = 1,
ProcessDate = dbo.BDATE(GETDATE())
FROM @Temp t
INNER JOIN tbl_BPMStockDelayOrders sdo ON t.OrderNum = sdo.OrderNum
AND t.Component = sdo.Component
AND t.WHID = sdo.WHID
WHERE t.ActualQty > 0
September 16, 2008 at 3:43 pm
Wow, completely different way of thinking... many thanks. Hopefully I'll start mastering the dark art 😀
September 17, 2008 at 10:25 am
Not sure, but I think that there should be a clustered index on the temporary table as well. Not exactly sure which columns. Jeff Moden wrote a nice article on Running Totals that might be helpful here.
😎
September 17, 2008 at 10:33 am
Ever since I found out about how to do Running Totals (a couple of years ago), that has been my Hammer for so many things. I haven't yet had a problem with my temp tables without a clustered index. But I am always really careful to make sure that I load the data into the temp table in the exact order that I need to process the data in. If this isn't enough, I would be happy to hear from someone with some clarification on that. I sure don't want my procs to start breaking on me.
Scott
September 17, 2008 at 10:53 am
Scott,
This is the article that Jeff wrote and is a good place to start.
http://www.sqlservercentral.com/articles/Advanced+Querying/61716/
😎
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply