mainrotor
asked on
I need help with a Scalar variable error in SQL Server
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,StudentScho olStartDat e,StudentS choolExitD ate,SiteID ,
StudentLegalLastName, StudentLegalFirstName, StudentLegalMiddleName,Stu dentBirthD ate, 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_Enrollm ent,LocalS tudentID,S iteID,[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_Enrollm ent 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
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,StudentScho
StudentLegalLastName, StudentLegalFirstName, StudentLegalMiddleName,Stu
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_Enrollm
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_Enrollm
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sorry, that post wasn't there when I was typing mine out. :)
ASKER
Thanks knightEknight for your post, but I have to award the points to Isavidge.
INSERT INTO @TempAttendance(ID_Enrollm
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]