[dba-SQLServer] Joining temp tables in SPROC

Hi David,

what is the difference between Temp table and using a variable tables.  I
never used such an exotic thing ?

thanks

Mark


On 30 August 2011 20:42, newsgrps <newsgrps at dalyn.co.nz> wrote:

> 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 ______________________________**
>> **_________________
>>
>
> ______________________________**_________________
> 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>
>
>