We help IT Professionals succeed at work.

I need help with a Scalar variable error in SQL Server

mainrotor
mainrotor used Ask the Experts™
on
Hi all,
I get this error: Must declare the scalar variable "@TempEnrollment".
When trying to execute the following SQL Syntax in SQL Server Management Studio.  Please tell me what  I need to do to fix this?

Thanks in advance,
mrotor

DECLARE @dtePeriodStartDate datetime;
DECLARE @dtePeriodEndDate datetime;

SET @dtePeriodStartDate = '7/1/2010';
SET @dtePeriodEndDate = '6/30/2011';

--*****************************
--BUILD ENROLLMENT INFO TABLE
DECLARE @TempEnrollment TABLE
(
      PK int Identity(1,1),
      ID_Enrollment int,
      LocalStudentID nvarchar(25),
      StudentSchoolStartDate datetime,
      StudentSchoolExitDate datetime,
      SiteID nvarchar(50),      
      StudentLegalLastName nvarchar(50),
      StudentLegalFirstName nvarchar(30),
      StudentLegalMiddleName nvarchar(30),
      StudentBirthDate datetime,
         StudentGenderCode nvarchar(1),
         SSID nvarchar(10)
)

INSERT INTO @TempEnrollment (ID_Enrollment, LocalStudentID,StudentSchoolStartDate,StudentSchoolExitDate,SiteID,
                  StudentLegalLastName, StudentLegalFirstName, StudentLegalMiddleName,StudentBirthDate, StudentGenderCode,SSID)
SELECT Enrollment.ID_Enrollment, Enrollment.[Local Student ID], Enrollment.[Student School Start Date], Enrollment.[Student School Exit Date], Enrollment.SiteID,
       Student.[Student Legal Last Name], Student.[Student Legal First Name], Student.[Student Legal Middle Name], Student.[Student Birth Date],
       Student.[Student Gender Code], Student.SSID
FROM Enrollment INNER JOIN
     Student ON Enrollment.[Local Student ID] = Student.[Local Student ID]
WHERE (Enrollment.[Student School Exit Date] >= @dtePeriodStartDate AND Enrollment.[Student School Start Date] <= @dtePeriodEndDate)
            AND Enrollment.SiteID <> 'ISS'
GROUP BY Enrollment.ID_Enrollment, Enrollment.[Local Student ID], Enrollment.[Student School Start Date], Enrollment.[Student School Exit Date], Enrollment.SiteID,
         Student.[Student Legal Last Name], Student.[Student Legal First Name], Student.[Student Legal Middle Name], Student.[Student Birth Date],
         Student.[Student Gender Code], Student.SSID
ORDER BY Enrollment.[Local Student ID], Enrollment.[Student School Start Date] DESC



--*****************************
--BUILD ATTENDANCE INFO TABLE
DECLARE @TempAttendance TABLE
(
      PK int Identity(1,1),
      ID_Enrollment int,
      LocalStudentID nvarchar(25),
      SiteID nvarchar(50),      
      [Day Of Attendance] datetime
)
INSERT INTO @TempAttendance(ID_Enrollment,LocalStudentID,SiteID,[Day Of Attendance])
SELECT  Attendence.ID_Enrollment, Attendence.[Local Student ID], @TempEnrollment.SiteID, Attendence.[Date of Attendance]
FROM    Attendence INNER JOIN @TempEnrollment ON Attendence.ID_Enrollment = @TempEnrollment.ID_Enrollment AND
        Attendence.[Date of Attendance] >= @TempEnrollment.[Student School Start Date]  AND
        Attendence.[Date of Attendance] <= @TempEnrollment.[Student School Exit Date]
ORDER BY Attendence.[Local Student ID], Attendence.[Date of Attendance]

SELECT * FROM @TempAttendance


Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
In the second query, give the table variable an alias.

Lee
--*****************************
--BUILD ATTENDANCE INFO TABLE
DECLARE @TempAttendance TABLE
(
      PK int Identity(1,1),
      ID_Enrollment int,
      LocalStudentID nvarchar(25),
      SiteID nvarchar(50),      
      [Day Of Attendance] datetime
)
NSERT INTO @TempAttendance(ID_Enrollment,LocalStudentID,SiteID,[Day Of Attendance])
SELECT  Attendence.ID_Enrollment, Attendence.[Local Student ID], a.SiteID, Attendence.[Date of Attendance]
FROM    Attendence INNER JOIN @TempEnrollment a ON Attendence.ID_Enrollment = a.ID_Enrollment AND
        Attendence.[Date of Attendance] >= a.[Student School Start Date]  AND
        Attendence.[Date of Attendance] <= a.[Student School Exit Date]
ORDER BY Attendence.[Local Student ID], Attendence.[Date of Attendance]

Open in new window

What happens if you alias the table variable in the last query?

INSERT INTO @TempAttendance(ID_Enrollment,LocalStudentID,SiteID,[Day Of Attendance])
SELECT  Attendence.ID_Enrollment, Attendence.[Local Student ID], T.SiteID, Attendence.[Date of Attendance]
FROM    Attendence INNER JOIN @TempEnrollment T ON Attendence.ID_Enrollment = T.ID_Enrollment AND
        Attendence.[Date of Attendance] >= T.[Student School Start Date]  AND
        Attendence.[Date of Attendance] <= T.[Student School Exit Date]
ORDER BY Attendence.[Local Student ID], Attendence.[Date of Attendance]
sorry, that post wasn't there when I was typing mine out.  :)

Author

Commented:
Thanks knightEknight for your post, but I have to award the points to Isavidge.