June 29, 2017 at 2:46 pm
The script should populate a temp table with a list of item numbers based on a date range and then exectute a stored procedure on each item in the list. The stored procedure operates on 4 parameters- ItemNumber, Operation, Start date and End Date. The procedure runs a set of calculations for each item number and inserts the results into another table. If I execute the procedure manually for one item it runs fine and inserts the results in the table as expected.
The date range I have been using puts 387 items in the 'pick list'. In theory the sproc should be called 387 times and insert 387 sets of rows in the final table. When I execute the script it runs for about 15 seconds and completes without error. Nothing is inserted in the table.The messages window shows '(387 row(s) affected)' - (obviously the item pick list) followed by '(0 row(s) affected)' 2023 times. 2023 seems like a reasonable number for how many records that should be entered in the table.
Can anyone tell me what have I done wrong with this loop? Thanks in advance for your help!
My script:
IF OBJECT_ID('tempdb..#Item_Pick_List') IS NOT NULL
BEGIN
DROP TABLE #Item_Pick_List
END
DECLARE @NumberRecords INT
, @RowCount INT
DECLARE @ItemNumber CHAR(18)
DECLARE @Operation INT
DECLARE @StartDate SMALLDATETIME
DECLARE @EndDate SMALLDATETIME
SET @StartDate = '01/01/2017'
SET @EndDate = '02/28/2017'
CREATE TABLE #Item_Pick_List(
RowID INT IDENTITY(1, 1)
, ItemNum CHAR(18)
)
INSERT INTO #Item_Pick_List (
ItemNum
)
SELECT DISTINCT ITEM
FROM WORK_ORDERS AS WHM
WHERE WHM.DATE_CLOSED BETWEEN @StartDate AND @EndDate
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1
WHILE @RowCount <= @NumberRecords
BEGIN
SELECT @ItemNumber = ItemNum
FROM #Item_Pick_List
WHERE RowID = @RowCount
EXEC USP_GetVariance @ItemNumber, @Operation, @StartDate, @EndDate
SET @RowCount = @RowCount + 1
END
DROP TABLE #Item_Pick_List
June 29, 2017 at 2:58 pm
How does USP_GetVariance work when @Operation is NULL, as it will be here? That variable is declared but never assigned a value.
Cheers!
June 29, 2017 at 3:10 pm
Jacob Wilkins - Thursday, June 29, 2017 2:58 PMHow does USP_GetVariance work when @Operation is NULL, as it will be here? That variable is declared but never assigned a value.Cheers!
It would run without error and return no results just like the problem I described.That was the problem. I am mortified that I missed something so simple! I think that's enough SQL for me for the day.
Thank you for taking the time to look at it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply