November 16, 2009 at 11:31 am
I have the following stored procedure that is using a declared temporary table, however the data being returned into the report seems to be the select statement inside this procedure for the temp table. I am not getting * from @report, but I am getting * from dbo.tblGlueRoomStock. Does anybody know why this would be?
CREATE PROC dbo.GlueRoomInventory
AS
-----------------------------------------------
EXEC dbo.usp_GlueRoom
SET NOCOUNT ON
DECLARE @report TABLE
(
uid int primary key clustered,
caseCode char(10) not null,
scheduledDate dateTime not null,
shift char(1) not null,
line char(10) not null,
CartonCopy nvarchar(20) not null,
CubeSize int not null,
CasesRan int null,
CartonsPerCase int not null,
GluedPerSkid int not null,
CartonsPerSkid int not null,
TotalGlued int not null,
TotalFlat int not null,
StartDate datetime not null,
EndDate datetime not null,
EightHrUsage decimal(8,2) not null,
scheduledCases int not null,
totalScheduledCases int not null,
currentInventory int not null,
ShiftInventoryWillRunOut BIT NULL
)
DECLARE @uid int
,@caseCode char(10)
,@CaseCodeTracker char(10)
,@scheduledDate dateTime
,@shift char(1)
,@line char(10)
,@cartonCopy nvarchar(20)
,@cubeSize int
,@CasesRan int
,@CartonsPerCase int
,@GluedPerSkid int
,@CartonsPerSkid int
,@TotalGlued int
,@TotalFlat int
,@StartDate datetime
,@EndDate datetime
,@EightHrUsage decimal(8,2)
,@scheduledCases int
,@totalScheduledCases int
,@currentInventory int
,@ShiftInventoryWillRunOut BIT
,@DoneWithThisCaseCode BIT
DECLARE runningTotalsCursor CURSOR FOR
SELECTuid
,caseCode
,ScheduledDate
,Shift
,line
,CartonCopy
,cubesize
,CasesRan
,CartonsPerCase
,GluedPerSkid
,CartonsPerSkid
,TotalGlued
,TotalFlat
,StartDate
,EndDate
,EightHrUsage
,CasesScheduled
,coalesce(CurrentGluedPieces,0) as CurrentGluedPieces
FROMdbo.tblGlueRoomStock
ORDERBY
caseCode
,scheduledDate
,shift
,line
OPENrunningTotalsCursor
SET@totalScheduledCases = 0
---------------------------------------------------------------
-- Perform the first fetch.
---------------------------------------------------------------
FETCHNEXT FROM RunningTotalsCursor
INTO@uid
,@caseCode
,@scheduledDate
,@line
,@cartonCopy
,@cubeSize
,@CasesRan
,@CartonsPerCase
,@GluedPerSkid
,@CartonsPerSkid
,@TotalGlued
,@TotalFlat
,@StartDate
,@EndDate
,@EightHrUsage
,@scheduledCases
,@currentInventory
---------------------------------------------------------------
-- @CaseCodeTracker and @caseCode are now the same.
---------------------------------------------------------------
SET@CaseCodeTracker= @caseCode
SET@ShiftInventoryWillRunOut= 0
SET@DoneWithThisCaseCode= 0
---------------------------------------------------------------
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
---------------------------------------------------------------
WHILE@@FETCH_STATUS = 0
BEGIN
----------------------------------------------------------------------
-- First loop @CaseCodeTracker and @caseCode are equal.
-- Check it every loop.
-- When it changes reset the count of @totalScheduledCases to zero.
----------------------------------------------------------------------
IF@CaseCodeTracker <> @caseCode
BEGIN
SET@totalScheduledCases= 0
SET@CaseCodeTracker= @caseCode
SET@DoneWithThisCaseCode= 0
END
----------------------------------------------------------------------
-- Increment @totalScheduledCases for the given casecode.
----------------------------------------------------------------------
SET@totalScheduledCases = @totalScheduledCases + @scheduledCases
----------------------------------------------------------------------
-- Set a flag to indicate the first shift where the inventory will be
-- depleted.
----------------------------------------------------------------------
SET@ShiftInventoryWillRunOut =
CASE
WHEN( @DoneWithThisCaseCode = 0 AND @currentInventory <= @totalScheduledCases )
THEN 1
END
IF@ShiftInventoryWillRunOut = 1
BEGIN
SET@DoneWithThisCaseCode = 1
END
INSERT INTO@report(uid,caseCode, CartonCopy, scheduledDate, shift, line, scheduledCases, cubeSize, casesRan, CartonsPerCase, GluedPerSkid, CartonsPerSkid, TotalGlued, TotalFlat, StartDate, EndDate, EightHrUsage, totalScheduledCases, currentInventory, ShiftInventoryWillRunOut)
VALUES (@uid,@caseCode, @cartoncopy, @scheduledDate, @shift, @line, @scheduledCases, @cubeSize, @casesRan, @CartonsPerCase, @GluedPerSkid, @CartonsPerSkid, @TotalGlued, @TotalFlat, @StartDate, @EndDate, @EightHrUsage, @totalScheduledCases, @currentInventory, @ShiftInventoryWillRunOut)
------------------------------------------------------------
-- This is executed as long as the previous fetch succeeds.
------------------------------------------------------------
FETCHNEXT FROM RunningTotalsCursor
INTO@uid
,@caseCode
,@scheduledDate
,@line
,@cartonCopy
,@cubeSize
,@casesRan
,@CartonsPerCase
,@GluedPerSkid
,@CartonsPerSkid
,@TotalGlued
,@TotalFlat
,@StartDate
,@EndDate
,@EightHrUsage
,@scheduledCases
,@currentInventory
END
CLOSErunningTotalsCursor
DEALLOCATErunningTotalsCursor
SELECT*
FROM@report
ORDERBY
caseCode
,scheduledDate
,shift
,line
SET NOCOUNT OFF
November 16, 2009 at 12:00 pm
Found the issue. I was selecting * in the primary stored procedure so it was returning two datasets and that one is the initial one. Commented that out and it works.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply