I have the below in a stored procedure. I needed to have the report information in one recordset because a Crystal Report will be viewing it. Here's my question, it's on the UPDATE statement... it works if I replace @LogReport.Username with a username, like 'iufits', but when I use @LogReport.Username in the where clause I get the error message: "Must declare the scalar variable "@LogReport". I'm using SQL Server 2005.
What am I missing on the Update statement?
Declare @LogReport Table (
Username varchar (20) NOT NULL ,
LoginsTotal int NOT NULL,
LoginsLastWeek int NOT NULL,
LoginsLast30Days int NOT NULL,
LoginsLastYear int NOT NULL
Insert Into @LogReport (Username, LoginsTotal, LoginsLastWeek, LoginsLast30Days, LoginsLastYear) Select Username, Count(Username), 0, 0, 0 As Logins From LOG_FILE Where LOG_ENTRY='Login' GROUP BY Username
Set LoginsLastWeek = T2.Logins
From (Select Username, Count(Username) As Logins From LOG_FILE Where LOG_TIME > (getDate() - 7) And LOG_ENTRY='Login' GROUP BY Username) T2
Where T2.Username = @LogReport.Username