[dba-SQLServer] Joining temp tables in SPROC

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 ______________________________**_________________
> dba-SQLServer mailing list
> dba-SQLServer@**databaseadvisors.com <dba-SQLServer at databaseadvisors.com>
> http://databaseadvisors.com/**mailman/listinfo/dba-sqlserver<http://databaseadvisors.com/mailman/listinfo/dba-sqlserver>
> http://www.databaseadvisors.**com <http://www.databaseadvisors.com>
>
>