[dba-SQLServer] Joining temp tables in SPROC

Group,

Can someone please explain why this is

I get an error "Must declare the variable ttmpWeeks" when I try to 
save the following in a stored procedure:

ALTER PROCEDURE sprptProductionSummary
         (
                 @txtDate1 varchar(20),
                 @txtDate2 varchar(20)
         )
AS
         SET NOCOUNT ON

         SET DATEFIRST 1 -- Monday

         DECLARE @ttmpWeeks table (WeekNo tinyint)

         DECLARE @tmpDate as datetime

         SET @tmpDate = @txtDate1

         INSERT INTO @ttmpWeeks (WeekNo)
         VALUES (DATEPART(week, @tmpDate))

         SET @tmpDate = DATEADD(week, 1, @tmpDate)

         WHILE @tmpdate < @txtDate2
                 BEGIN
                         INSERT INTO @ttmpWeeks (WeekNo)
                         VALUES (DATEPART(week, @tmpDate))

                         SET @tmpDate = DATEADD(week, 1, @tmpDate)
                 END

         SELECT WeekNo, ISNULL(spKnit.KnittedWeight,0) AS KnittedWeight
         FROM @ttmpWeeks LEFT OUTER JOIN (SELECT DATEPART(week, 
dbo.tblRoll.KnitDate) AS WeekNum, ISNULL(SUM(KnitWeight),0) AS KnittedWeight
                                                 FROM dbo.tblRoll
                                                 WHERE KnitDate 
BETWEEN @txtDate1 AND @txtDate2
                                                 GROUP BY 
DATEPART(week, dbo.tblRoll.KnitDate)) AS spKnit ON @ttmpWeeks.WeekNo 
= spKnit.WeekNum

{rest of code continues}

However, if I change the last line to
GROUP BY DATEPART(week, dbo.tblRoll.KnitDate)) AS spKnit ON WeekNo = 
spKnit.WeekNum
it saves and works.

Why doesn't it accept the table part of the WeekNo field?


Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand