[dba-SQLServer] Joining temp tables in SPROC

Thanks Francisco.  That worked and makes me more comfortable.

David

At 31/08/2011, Francisco Tapia wrote:
>i'm not sure, but i've always used an alias when I join my temp tables
>including variable tables, so my change would read
>
>FROM @ttmpWeeks AS tWeeks
>
>then your ON statment can read tWeeks.WeekNo = join clause
>
>-Francisco
>http://bit.ly/sqlthis   | Tsql and More...
><http://db.tt/JeXURAx>
>
>
>
>
>On Tue, Aug 30, 2011 at 1:41 AM, newsgrps <newsgrps at dalyn.co.nz> wrote:
>
> > 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 ______________________________**_________________