December 28, 2007 at 9:55 am
Good Morning.
I have MANY statements like the one below in a report. Each day I extract data into a table and name it using the date, in this example PersonnelComparison122607. It is compared to the previous/following date, whatever the case may be, here 122707.
I would like to declare something like this, but for the table names. I don't see a way to do it.:
--declare Date Variable -- Current Process Date @pd
USE smcscalc
Declare @pd datetime
set @pd = '11/1/07'--Set process date here**************
Is there a way to do this? A better way to go about this? This is a new report. And I'm certain there is a better (and less likely to cause error) way than changing the file names in the report each day.
INSERT
INTO smcsarchive..PersonnelChanges2008
(DateReported, OWN, Name, EmployeeStatus, FullorPartTime, GradeLevel, Title, JobEntryDate,
OriginalHireDate, CurrentHireDate, TermDate1, LOAStartDate1, LOAReturnDate1,
DepartmentDesc, DepartmentEntryDate, MgrOWN, ManagersName, OTC, TypeCode, JobCode
)
SELECT
a.DateReported, a.SSN, a.Name, a.EmployeeStatus, a.FullorPartTime,
a.GradeLevel, a.Title, a.JobEntryDate, a.OriginalHireDate, a.CurrentHireDate,
a.TermDate1, a.LOAStartDate1, a.LOAReturnDate1, a.DepartmentDesc, a.DepartmentEntryDate,
a.ManagerSSN, a.ManagersName, a.OTC, a.TypeCode, a.JobCode
from
smcsarchive..PersonnelComparison122607 b
, smcsarchive..PersonnelComparison122707 a
WHERE
a.SSN = b.SSN
and
a.JobCode <> b.JobCode
Thanks for looking.
December 28, 2007 at 10:12 am
No, in SQL2000 you cannot have table names as variables in a procedure.
But you can create the SQL in a variable dynamically, and execute the SQL. For example (not tested, just written here on the fly):
DECLARE @todaytbl varchar(50)
DECLARE @sql varchar(2000)
SET @todaytbl = 'mytable_' + Year(getdate()) + Month(getdate()) + Day(getdate())
SET @sql = 'SELECT * FROM ' + @todaytbl
EXEC @sql
Of course, you will want to put some error handling on this, to make sure the statement is correct, table exists, results returned, etc.
Hope this helps
Mark
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply