February 17, 2010 at 12:36 pm
I'm working on a cursor that i'm going through a recordset and i have to add some if statements to it. Problem i'm having is i'm not getting the result i was expecting its almost like its not hitting the if.
--Automated Transfer
Declare @PartNumber as nvarchar(60)
Declare @Location as nvarchar(60)
Declare @Quantity as int
Declare @PartDescription as nvarchar(60)
Declare @Pkey as int
--Variables used to determine if we have enough of the part to make the transfer
Declare @DistribQoH as int
Declare @CurrentPartNumber as nvarchar(60)
Declare @TransferQtyCounter as int
set @CurrentPartNumber=''
set @TransferQtyCounter=0
DECLARE AutomatedReorders CURSOR FOR
Select PartNumber, Location, Quantity, DistribQoH from sometable
order by PartNumber
OPEN AutomatedReorders
FETCH NEXT FROM AutomatedReorders
INTO @Location, @PartNumber, @PartDescription, @Quantity, @DistribQoH
WHILE @@FETCH_STATUS = 0
Begin
If @CurrentPartNumber<>@PartNumber
--Do something
Print 'equal'
@CurrentPartNumber=@ParNumber
Else
@TransferQtyCounter=@TransferQtyCounter+@Quantity
If @TransferQtyCounter<=@DistribQoH
--Do Something Else
Print 'notequal'
FETCH NEXT FROM AutomatedReorders
INTO @Location, @PartNumber, @PartDescription, @Quantity, @DistribQoH
End
CLOSE AutomatedReorders
DEALLOCATE AutomatedReorders
RETURN
If i had a recordset of :
Part Location Qty DistribQoH
a GR 1 2
b CH 1 2
c DT 1 1
c GR 1 1
What i would expect to see from after i executed the above is:
a
a
a
b
but what i get is
equal
equal
notequal
notequal
equal
equal
notequal
notequal
equal
equal
notequal
notequal
equal
equal
notequal
notequal
What am i doing wrong? I new to doing the logic in sql usually i do it in asp.
February 17, 2010 at 12:43 pm
First, you really should look for a set-based solution to this problem. The cursor-based just won't scale well. We don't have enough info to help you write a set-based solution. We'd need table def(s) (CREATE TABLE statement(s)), sample data (series of INSERT INTO statements for the table(s)), expected results.
Barring that, I looked at your code, and saw what may be part of your problem:
OPEN AutomatedReorders
FETCH NEXT FROM AutomatedReorders
INTO @Location, @PartNumber, @PartDescription, @Quantity, @DistribQoH
WHILE @@FETCH_STATUS = 0
Begin
If @CurrentPartNumber<>@PartNumber
begin
--Do something
Print 'equal'
@CurrentPartNumber=@ParNumber
end
Else
begin
@TransferQtyCounter=@TransferQtyCounter+@Quantity
If @TransferQtyCounter<=@DistribQoH
begin
--Do Something Else
Print 'notequal'
end
end
FETCH NEXT FROM AutomatedReorders
INTO @Location, @PartNumber, @PartDescription, @Quantity, @DistribQoH
End
CLOSE AutomatedReorders
DEALLOCATE AutomatedReorders
RETURN
February 17, 2010 at 12:46 pm
Well, first off, the code you've posted won't pass the compiler as it contains invalid SQL statements so it can't be producing what you've shown.
This seems like a pretty simple one, but we'll need some more info from you to help more. Please read through the thread in my signature line and post example data and a working copy of your query.
Also, post a description of the process that you are writing this for. You don't need a cursor for this, but it's hard to tell you how to re-write it when we don't know what you are trying to accomplish.
February 17, 2010 at 12:49 pm
Also, you cannot do variable assignments "bare" in T-SQL, you have to put a "SELECT " in front.
Like:
SELECT @myVariable = @someOtherVariable
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 17, 2010 at 12:50 pm
In addition to the corrections which Lynn pointed out:
If @CurrentPartNumber<>@PartNumber
begin
--Do something
Print 'equal'
@CurrentPartNumber=@ParNumber
end
Note the mis-spelling. With the BEGIN/END statements in place, you should now have an undefined variable error.
Take Lynn's advice, describe in more detail what you are trying to do. Cursors aren't necessarily a bad thing, but they are real time & resource hogs and best avoided if there's a set-based solution, which there almost always is. Set-based solutions also tend to be "cleaner" than cursor-based and are often far easier to debug & maintain.
Edit: blimey, are you lot bored or what? I thought something was jamming the door shut, it was all the bodies behind it!
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 17, 2010 at 1:17 pm
Adding the Begin/End worked.
Sorry I didn't copy my exact code over i apologize, I did have some of the things in my original that were missed. What i'm trying to accomplish is i have a select statment that contains a list of parts that need to be reordered. What my cursor does is transitions through this set of parts and inserts and updates multiple tables. It also needs to verify if i have a part number that is the same for multiple locations it needs to check to make sure I still have the appropiate quantity in order to insert it, if theres not enough quantity it will insert. I commented out my inserts and updates.
Here is the basic code:
--Automated Transfer
Declare @PartNumber as nvarchar(60)
Declare @Location as nvarchar(60)
Declare @Quantity as int
Declare @PartDescription as nvarchar(60)
Declare @Pkey as int
--Variables used to determine if we have enough of the part to make the transfer
Declare @DistribQoH as int
Declare @CurrentPartNumber as nvarchar(60)
Declare @TransferQtyCounter as int
set @CurrentPartNumber=''
set @TransferQtyCounter=0
DECLARE AutomatedReorders CURSOR FOR
SELECT Location, PartNumber, PartDescription, Quantity, DistribQoH from InventReorderPoints
order by PartNumber
OPEN AutomatedReorders
FETCH NEXT FROM AutomatedReorders
INTO @Location, @PartNumber, @PartDescription, @Quantity, @DistribQoH
WHILE @@FETCH_STATUS = 0
Begin
If @CurrentPartNumber<>@PartNumber
Begin
Print @PartNumber +'a'
--INSERT INTO InventTransfers ( ItrStatus, ItrCreatedBy, ItrOrigin, ItrDestination, ItrPartNumber, ItrDescription, ItrQuantity, ItrType, ItrTicket, ItrSentTo, ItrDateSent, ItrTransferMethod, ItrComments) SELECT 'Unsent' as ItrStatus, 'Automated Reorder' AS ItrCreatedBy, 'DISTRIB' AS ItrOrigin, @Location AS ItrDestination, @PartNumber AS ItrPartNumber, @PartDescription AS ItrDescription, @Quantity AS ItrQuantity, 'Standard' AS ItrType, '0' AS ItrTicket, 'Anyone' AS ItrSentTo, GetDate() AS ItrDateSent, 'Not Sent Yet' AS ItrTransferMethod, 'Automated Reorder' AS ItrComments
DECLARE MaxPkey_Transfer CURSOR FOR
SELECT Max(InventTransfers.ItrPKey) AS ItrPKey FROM InventTransfers WHERE (((InventTransfers.ItrPartNumber)=@PartNumber) AND ((InventTransfers.ItrOrigin)='DISTRIB') AND ((InventTransfers.ItrDestination)=@Location)) and (ItrStatus='Unsent')
OPEN MaxPkey_Transfer
Fetch Next From MaxPkey_Transfer into @Pkey
If Not Exists(SELECT IhiPKey, IhiPartNumber, IhiLocationCode, IhiOrigionator, IhiTypePKey FROM InventHistory WHERE (IhiOrigionator = N'Automated Reorder') AND (IhiPartNumber = @PartNumber) AND (IhiLocationCode = @Location) AND (IhiTypePKey =@Pkey))
--INSERT INTO InventHistory ( IhiTypePKey, IhiDate, IhiPartNumber, IhiLocationCode, IhiQuantity, IhiOrigionator, IhiType, IhiDescription ) SELECT @Pkey AS IhiTypePKey, GetDate() AS IhiDate, @PartNumber AS IhiPartNumber, @Location AS IhiLocationCode, @Quantity AS IhiQuantity, 'Automated Reorder' AS IhiOrigionator, 'Transfer In' AS IhiType, 'From DISTRIB To' + @Location AS IhiDescription;
If Not Exists(SELECT IhiPKey, IhiPartNumber, IhiLocationCode, IhiOrigionator, IhiTypePKey FROM InventHistory WHERE (IhiOrigionator = N'Automated Reorder') AND (IhiPartNumber = @PartNumber) AND (IhiLocationCode = 'DISTRIB') AND (IhiTypePKey =@Pkey))
--INSERT INTO InventHistory ( IhiTypePKey, IhiDate, IhiPartNumber, IhiLocationCode, IhiQuantity, IhiOrigionator, IhiType, IhiDescription ) SELECT @Pkey AS IhiTypePKey, GetDate() AS IhiDate, @PartNumber AS IhiPartNumber, 'DISTRIB' AS IhiLocationCode, -@Quantity AS IhiQuantity, 'Automated Reorder' AS IhiOrigionator, 'Transfer Out' AS IhiType, 'From DISTRIB To'+ @Location AS IhiDescription;
CLOSE MaxPkey_Transfer
DEALLOCATE MaxPkey_Transfer
--UPDATE InventQuantities SET InventQuantities.IqtQtyTransferOut = IqtQtyTransferOut+@Quantity WHERE (((InventQuantities.IqtPartNumber)=@PartNumber) AND ((InventQuantities.IqtLocationCode)='DISTRIB'));
--UPDATE InventQuantities SET InventQuantities.IqtQtyTransferIn = [IqtQtyTransferIn]+@Quantity WHERE (((InventQuantities.IqtPartNumber)=@PartNumber) AND ((InventQuantities.IqtLocationCode)=@Location));
set @TransferQtyCounter=0
set @CurrentPartNumber=@PartNumber
set @TransferQtyCounter=@Quantity
End
else
Begin
set @TransferQtyCounter=@TransferQtyCounter+@Quantity
If @TransferQtyCounter<=@DistribQoH
Begin
Print @TransferQtyCounter
Print @DistribQoH
--INSERT INTO InventTransfers ( ItrStatus, ItrCreatedBy, ItrOrigin, ItrDestination, ItrPartNumber, ItrDescription, ItrQuantity, ItrType, ItrTicket, ItrSentTo, ItrDateSent, ItrTransferMethod, ItrComments) SELECT 'Unsent' as ItrStatus, 'Automated Reorder' AS ItrCreatedBy, 'DISTRIB' AS ItrOrigin, @Location AS ItrDestination, @PartNumber AS ItrPartNumber, @PartDescription AS ItrDescription, @Quantity AS ItrQuantity, 'Standard' AS ItrType, '0' AS ItrTicket, 'Anyone' AS ItrSentTo, GetDate() AS ItrDateSent, 'Not Sent Yet' AS ItrTransferMethod, 'Automated Reorder' AS ItrComments
DECLARE MaxPkey_Transfer CURSOR FOR
SELECT Max(InventTransfers.ItrPKey) AS ItrPKey FROM InventTransfers WHERE (((InventTransfers.ItrPartNumber)=@PartNumber) AND ((InventTransfers.ItrOrigin)='DISTRIB') AND ((InventTransfers.ItrDestination)=@Location)) and (ItrStatus='Unsent')
OPEN MaxPkey_Transfer
Fetch Next From MaxPkey_Transfer into @Pkey
If Not Exists(SELECT IhiPKey, IhiPartNumber, IhiLocationCode, IhiOrigionator, IhiTypePKey FROM InventHistory WHERE (IhiOrigionator = N'Automated Reorder') AND (IhiPartNumber = @PartNumber) AND (IhiLocationCode = @Location) AND (IhiTypePKey =@Pkey))
--INSERT INTO InventHistory ( IhiTypePKey, IhiDate, IhiPartNumber, IhiLocationCode, IhiQuantity, IhiOrigionator, IhiType, IhiDescription ) SELECT @Pkey AS IhiTypePKey, GetDate() AS IhiDate, @PartNumber AS IhiPartNumber, @Location AS IhiLocationCode, @Quantity AS IhiQuantity, 'Automated Reorder' AS IhiOrigionator, 'Transfer In' AS IhiType, 'From DISTRIB To' + @Location AS IhiDescription;
If Not Exists(SELECT IhiPKey, IhiPartNumber, IhiLocationCode, IhiOrigionator, IhiTypePKey FROM InventHistory WHERE (IhiOrigionator = N'Automated Reorder') AND (IhiPartNumber = @PartNumber) AND (IhiLocationCode = 'DISTRIB') AND (IhiTypePKey =@Pkey))
--INSERT INTO InventHistory ( IhiTypePKey, IhiDate, IhiPartNumber, IhiLocationCode, IhiQuantity, IhiOrigionator, IhiType, IhiDescription ) SELECT @Pkey AS IhiTypePKey, GetDate() AS IhiDate, @PartNumber AS IhiPartNumber, 'DISTRIB' AS IhiLocationCode, -@Quantity AS IhiQuantity, 'Automated Reorder' AS IhiOrigionator, 'Transfer Out' AS IhiType, 'From DISTRIB To'+ @Location AS IhiDescription;
CLOSE MaxPkey_Transfer
DEALLOCATE MaxPkey_Transfer
--UPDATE InventQuantities SET InventQuantities.IqtQtyTransferOut = IqtQtyTransferOut+@Quantity WHERE (((InventQuantities.IqtPartNumber)=@PartNumber) AND ((InventQuantities.IqtLocationCode)='DISTRIB'));
--UPDATE InventQuantities SET InventQuantities.IqtQtyTransferIn = [IqtQtyTransferIn]+@Quantity WHERE (((InventQuantities.IqtPartNumber)=@PartNumber) AND ((InventQuantities.IqtLocationCode)=@Location));
End
End
FETCH NEXT FROM AutomatedReorders
INTO @Location, @PartNumber, @PartDescription, @Quantity, @DistribQoH
End
CLOSE AutomatedReorders
DEALLOCATE AutomatedReorders
RETURN
I didn't think this could truly be done with sets. I've looked into the Merge command but it seemed complicated and i didn't see the flexibility that I would have using the cursor. It may not be as efficiant but seems to work. I'm going to be putting this in a stored procedure thats gonna run every 10 mins.
February 17, 2010 at 1:22 pm
And nested cursors don't scale very well at all.
If you provide the info requested, we could probably provide you with a much more scalable and mainatainable set based solution.
February 17, 2010 at 1:47 pm
Ok here is what i belive you want.
Create Tables and Data:
CREATE TABLE [dbo].[InventReorder](
[Location] [nvarchar](50) NOT NULL,
[PartNumber] [nvarchar](50) NOT NULL,
[Quantity] [int] NOT NULL,
[DistribQoh] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO InventReorder
(Location, PartNumber, Quantity, DistribQoh)
Select 'NV-STOCK', '00N7743','1','2'UNION ALL
Select 'NV-STOCK', '00N8953','1','8' UNION ALL
Select 'CH-STOCK', '00P3068','1','3' UNION ALL
Select 'NV-STOCK', '01K9862','1','4' UNION ALL
Select 'CH-STOCK', '314670-001','1','2' UNION ALL
Select 'CI-STOCK', '314670-001','1','2' UNION ALL
Select 'NV-STOCK', 'J9058','1','1' UNION ALL
Select 'FL-STOCK', 'J9058','1','1'
CREATE TABLE [dbo].[InventTransfers](
[ItrPKey] [int] IDENTITY(1,1) NOT NULL,
[ItrStatus] [nvarchar](50) NOT NULL,
[ItrConsumed] [bit] NULL,
[ItrOrigin] [nvarchar](50) NULL,
[ItrDestination] [nvarchar](50) NULL,
[ItrPartNumber] [nvarchar](50) NULL,
[ItrDescription] [nvarchar](100) NULL,
[ItrQuantity] [int] NULL,
[ItrReceivedQty] [int] NULL,
[ItrType] [nvarchar](50) NULL,
[ItrTicket] [bigint] NULL,
[ItrCreatedBy] [nvarchar](50) NULL,
[ItrDateSent] [datetime] NULL,
[ItrRecievedDate] [datetime] NULL,
[ItrRecievedBy] [nvarchar](50) NULL,
[ItrSentTo] [nvarchar](50) NULL,
[ItrComments] [ntext] NULL,
[ItrTransferMethod] [nvarchar](50) NULL,
[ItrSameDay] [bit] NULL,
CONSTRAINT [PK_InventTransfers] PRIMARY KEY CLUSTERED
(
[ItrPKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[InventTransfers] ADD CONSTRAINT [DF_InventTransfers_ItrGPUpdate] DEFAULT ('Open') FOR [ItrStatus]
GO
ALTER TABLE [dbo].[InventTransfers] ADD CONSTRAINT [DF_InventTransfers_ItrConsumed] DEFAULT (0) FOR [ItrConsumed]
GO
ALTER TABLE [dbo].[InventTransfers] ADD CONSTRAINT [DF_InventTransfers_ItrQuantity] DEFAULT (0) FOR [ItrQuantity]
GO
ALTER TABLE [dbo].[InventTransfers] ADD CONSTRAINT [DF_InventTransfers_ItrQuantityRec] DEFAULT (0) FOR [ItrReceivedQty]
GO
ALTER TABLE [dbo].[InventTransfers] ADD CONSTRAINT [DF_InventTransfers_ItrSameDay] DEFAULT (0) FOR [ItrSameDay]
GO
CREATE TABLE [dbo].[InventHistory](
[IhiPKey] [int] IDENTITY(1,1) NOT NULL,
[IhiPartNumber] [nvarchar](50) NOT NULL,
[IhiLocationCode] [nvarchar](50) NOT NULL,
[IhiQuantity] [int] NOT NULL,
[IhiDate] [datetime] NOT NULL,
[IhiOrigionator] [nvarchar](50) NOT NULL,
[IhiType] [nvarchar](50) NOT NULL,
[IhiDescription] [nvarchar](200) NOT NULL,
[IhiTypePKey] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_InventHistory] PRIMARY KEY CLUSTERED
(
[IhiPKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[InventQuantities](
[IqtPKey] [int] IDENTITY(1,1) NOT NULL,
[IqtPartNumber] [nvarchar](50) NOT NULL,
[IqtLocationCode] [nvarchar](50) NOT NULL,
[IqtQtyOnHand] [int] NOT NULL,
[IqtQtyTransferIn] [int] NOT NULL,
[IqtQtyTransferOut] [int] NOT NULL,
[IqtQtyDOA] [int] NOT NULL,
[IqtQtyReceiver] [int] NOT NULL,
[IqtQtyReturn] [int] NOT NULL,
[IqtQtyVariance] [int] NOT NULL,
[IqtQtyAllocate] [int] NOT NULL,
[IqtQtyConsume] [int] NOT NULL,
[IqtReorderPoint] [int] NULL,
CONSTRAINT [PK_InventQuantities] PRIMARY KEY CLUSTERED
(
[IqtPKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[InventQuantities] ADD CONSTRAINT [DF_InventQuantities_IqtQtyOnHand] DEFAULT (0) FOR [IqtQtyOnHand]
GO
ALTER TABLE [dbo].[InventQuantities] ADD CONSTRAINT [DF_InventQuantities_IqtQtyTransfer] DEFAULT (0) FOR [IqtQtyTransferIn]
GO
ALTER TABLE [dbo].[InventQuantities] ADD CONSTRAINT [DF_InventQuantities_IqtQtyTransferOut] DEFAULT (0) FOR [IqtQtyTransferOut]
GO
ALTER TABLE [dbo].[InventQuantities] ADD CONSTRAINT [DF_InventQuantities_IqtQtyDOA] DEFAULT (0) FOR [IqtQtyDOA]
GO
ALTER TABLE [dbo].[InventQuantities] ADD CONSTRAINT [DF_InventQuantities_IqtQtyReceiver] DEFAULT (0) FOR [IqtQtyReceiver]
GO
ALTER TABLE [dbo].[InventQuantities] ADD CONSTRAINT [DF_InventQuantities_IqtQtyReturn] DEFAULT (0) FOR [IqtQtyReturn]
GO
ALTER TABLE [dbo].[InventQuantities] ADD CONSTRAINT [DF_InventQuantities_IqtQtyVariance] DEFAULT (0) FOR [IqtQtyVariance]
GO
ALTER TABLE [dbo].[InventQuantities] ADD CONSTRAINT [DF_InventQuantities_IqtQtyAllocate] DEFAULT (0) FOR [IqtQtyAllocate]
GO
ALTER TABLE [dbo].[InventQuantities] ADD CONSTRAINT [DF_InventQuantities_IqtQtyConsume] DEFAULT (0) FOR [IqtQtyConsume]
GO
ALTER TABLE [dbo].[InventQuantities] ADD CONSTRAINT [DF_InventQuantities_IqtReorderPoint] DEFAULT ((0)) FOR [IqtReorderPoint]
GO
--Automated Transfer
Declare @PartNumber as nvarchar(60)
Declare @Location as nvarchar(60)
Declare @Quantity as int
Declare @Pkey as int
--Variables used to determine if we have enough of the part to make the transfer
Declare @DistribQoH as int
Declare @CurrentPartNumber as nvarchar(60)
Declare @TransferQtyCounter as int
set @CurrentPartNumber=''
set @TransferQtyCounter=0
DECLARE AutomatedReorders CURSOR FOR
SELECT Location, PartNumber, Quantity, DistribQoh
FROM InventReorder
ORDER BY PartNumber
OPEN AutomatedReorders
FETCH NEXT FROM AutomatedReorders
INTO @Location, @PartNumber, @Quantity, @DistribQoH
WHILE @@FETCH_STATUS = 0
Begin
If @CurrentPartNumber<>@PartNumber
Begin
Print @PartNumber +'a'
INSERT INTO InventTransfers ( ItrStatus, ItrCreatedBy, ItrOrigin, ItrDestination, ItrPartNumber, ItrDescription, ItrQuantity, ItrType, ItrTicket, ItrSentTo, ItrDateSent, ItrTransferMethod, ItrComments) SELECT 'Unsent' as ItrStatus, 'Automated Reorder' AS ItrCreatedBy, 'DISTRIB' AS ItrOrigin, @Location AS ItrDestination, @PartNumber AS ItrPartNumber, @PartDescription AS ItrDescription, @Quantity AS ItrQuantity, 'Standard' AS ItrType, '0' AS ItrTicket, 'Anyone' AS ItrSentTo, GetDate() AS ItrDateSent, 'Not Sent Yet' AS ItrTransferMethod, 'Automated Reorder' AS ItrComments
DECLARE MaxPkey_Transfer CURSOR FOR
SELECT Max(InventTransfers.ItrPKey) AS ItrPKey FROM InventTransfers WHERE (((InventTransfers.ItrPartNumber)=@PartNumber) AND ((InventTransfers.ItrOrigin)='DISTRIB') AND ((InventTransfers.ItrDestination)=@Location)) and (ItrStatus='Unsent')
OPEN MaxPkey_Transfer
Fetch Next From MaxPkey_Transfer into @Pkey
If Not Exists(SELECT IhiPKey, IhiPartNumber, IhiLocationCode, IhiOrigionator, IhiTypePKey FROM InventHistory WHERE (IhiOrigionator = N'Automated Reorder') AND (IhiPartNumber = @PartNumber) AND (IhiLocationCode = @Location) AND (IhiTypePKey =@Pkey))
INSERT INTO InventHistory ( IhiTypePKey, IhiDate, IhiPartNumber, IhiLocationCode, IhiQuantity, IhiOrigionator, IhiType, IhiDescription ) SELECT @Pkey AS IhiTypePKey, GetDate() AS IhiDate, @PartNumber AS IhiPartNumber, @Location AS IhiLocationCode, @Quantity AS IhiQuantity, 'Automated Reorder' AS IhiOrigionator, 'Transfer In' AS IhiType, 'From DISTRIB To' + @Location AS IhiDescription;
If Not Exists(SELECT IhiPKey, IhiPartNumber, IhiLocationCode, IhiOrigionator, IhiTypePKey FROM InventHistory WHERE (IhiOrigionator = N'Automated Reorder') AND (IhiPartNumber = @PartNumber) AND (IhiLocationCode = 'DISTRIB') AND (IhiTypePKey =@Pkey))
INSERT INTO InventHistory ( IhiTypePKey, IhiDate, IhiPartNumber, IhiLocationCode, IhiQuantity, IhiOrigionator, IhiType, IhiDescription ) SELECT @Pkey AS IhiTypePKey, GetDate() AS IhiDate, @PartNumber AS IhiPartNumber, 'DISTRIB' AS IhiLocationCode, -@Quantity AS IhiQuantity, 'Automated Reorder' AS IhiOrigionator, 'Transfer Out' AS IhiType, 'From DISTRIB To'+ @Location AS IhiDescription;
CLOSE MaxPkey_Transfer
DEALLOCATE MaxPkey_Transfer
UPDATE InventQuantities SET InventQuantities.IqtQtyTransferOut = IqtQtyTransferOut+@Quantity WHERE (((InventQuantities.IqtPartNumber)=@PartNumber) AND ((InventQuantities.IqtLocationCode)='DISTRIB'));
UPDATE InventQuantities SET InventQuantities.IqtQtyTransferIn = [IqtQtyTransferIn]+@Quantity WHERE (((InventQuantities.IqtPartNumber)=@PartNumber) AND ((InventQuantities.IqtLocationCode)=@Location));
set @TransferQtyCounter=0
set @CurrentPartNumber=@PartNumber
set @TransferQtyCounter=@Quantity
End
else
Begin
set @TransferQtyCounter=@TransferQtyCounter+@Quantity
If @TransferQtyCounter<=@DistribQoH
Begin
Print @TransferQtyCounter
Print @DistribQoH
INSERT INTO InventTransfers ( ItrStatus, ItrCreatedBy, ItrOrigin, ItrDestination, ItrPartNumber, ItrDescription, ItrQuantity, ItrType, ItrTicket, ItrSentTo, ItrDateSent, ItrTransferMethod, ItrComments) SELECT 'Unsent' as ItrStatus, 'Automated Reorder' AS ItrCreatedBy, 'DISTRIB' AS ItrOrigin, @Location AS ItrDestination, @PartNumber AS ItrPartNumber, 'Description of Part' AS ItrDescription, @Quantity AS ItrQuantity, 'Standard' AS ItrType, '0' AS ItrTicket, 'Anyone' AS ItrSentTo, GetDate() AS ItrDateSent, 'Not Sent Yet' AS ItrTransferMethod, 'Automated Reorder' AS ItrComments
DECLARE MaxPkey_Transfer CURSOR FOR
SELECT Max(InventTransfers.ItrPKey) AS ItrPKey FROM InventTransfers WHERE (((InventTransfers.ItrPartNumber)=@PartNumber) AND ((InventTransfers.ItrOrigin)='DISTRIB') AND ((InventTransfers.ItrDestination)=@Location)) and (ItrStatus='Unsent')
OPEN MaxPkey_Transfer
Fetch Next From MaxPkey_Transfer into @Pkey
If Not Exists(SELECT IhiPKey, IhiPartNumber, IhiLocationCode, IhiOrigionator, IhiTypePKey FROM InventHistory WHERE (IhiOrigionator = N'Automated Reorder') AND (IhiPartNumber = @PartNumber) AND (IhiLocationCode = @Location) AND (IhiTypePKey =@Pkey))
INSERT INTO InventHistory ( IhiTypePKey, IhiDate, IhiPartNumber, IhiLocationCode, IhiQuantity, IhiOrigionator, IhiType, IhiDescription ) SELECT @Pkey AS IhiTypePKey, GetDate() AS IhiDate, @PartNumber AS IhiPartNumber, @Location AS IhiLocationCode, @Quantity AS IhiQuantity, 'Automated Reorder' AS IhiOrigionator, 'Transfer In' AS IhiType, 'From DISTRIB To' + @Location AS IhiDescription;
If Not Exists(SELECT IhiPKey, IhiPartNumber, IhiLocationCode, IhiOrigionator, IhiTypePKey FROM InventHistory WHERE (IhiOrigionator = N'Automated Reorder') AND (IhiPartNumber = @PartNumber) AND (IhiLocationCode = 'DISTRIB') AND (IhiTypePKey =@Pkey))
INSERT INTO InventHistory ( IhiTypePKey, IhiDate, IhiPartNumber, IhiLocationCode, IhiQuantity, IhiOrigionator, IhiType, IhiDescription ) SELECT @Pkey AS IhiTypePKey, GetDate() AS IhiDate, @PartNumber AS IhiPartNumber, 'DISTRIB' AS IhiLocationCode, -@Quantity AS IhiQuantity, 'Automated Reorder' AS IhiOrigionator, 'Transfer Out' AS IhiType, 'From DISTRIB To'+ @Location AS IhiDescription;
CLOSE MaxPkey_Transfer
DEALLOCATE MaxPkey_Transfer
UPDATE InventQuantities SET InventQuantities.IqtQtyTransferOut = IqtQtyTransferOut+@Quantity WHERE (((InventQuantities.IqtPartNumber)=@PartNumber) AND ((InventQuantities.IqtLocationCode)='DISTRIB'));
UPDATE InventQuantities SET InventQuantities.IqtQtyTransferIn = [IqtQtyTransferIn]+@Quantity WHERE (((InventQuantities.IqtPartNumber)=@PartNumber) AND ((InventQuantities.IqtLocationCode)=@Location));
End
End
FETCH NEXT FROM AutomatedReorders
INTO @Location, @PartNumber, @Quantity, @DistribQoH
End
CLOSE AutomatedReorders
DEALLOCATE AutomatedReorders
RETURN
K this should be everything, let me know if you can help me make this more efficiant. This accomplishes everything i'm trying to do but if i could make it more efficiant without add more complexity that would be great.
February 17, 2010 at 5:07 pm
Why are all of the INSERTs and UPDATEs commented out?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 18, 2010 at 7:12 am
currently they were commented out for testing purposes, because i testing the if statements.
February 18, 2010 at 9:13 am
So, should we uncomment them? Because if I leave them commented, then its not doing very much.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 18, 2010 at 9:14 am
yes i went back and uncommented it in my example. thanks for your help
February 20, 2010 at 10:25 pm
FYI: @PartDescription is not defined.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 20, 2010 at 11:02 pm
Also there's no Primary Key on InventReorder.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 21, 2010 at 1:25 am
RBarryYoung (2/20/2010)
FYI: @PartDescription is not defined.
It was defined in the previous posts as NVARCHAR(60). The destination column appears to be NVARCHAR(100) though.
I am going with NVARCHAR(100), just to be on the safe side.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply