Morning.I am trying to write a query that dynamically generates the database name. the idea is the finance people want to query the "frozen" databases to get labor numbers. This is what i have so far, every time I try to run it it doesn't like my join and I get the message:Msg 102, Level 15, State 1, Line 22Incorrect syntax near 'L2'.Declare @CreateLastDBName varchar(100);Declare @CreateLast2DBName varchar(100);Declare @LastMthDate varchar(100);Declare @Last2MthDate varchar(100);Declare @Sql varchar(MAX);SELECT @LastMthDate= CAST(YEAR(GETDATE()) AS VARCHAR(4)) + left(CONVERT(char(20),DATEADD(MONTH,-1,GETDATE()),101),2)SELECT @Last2MthDate= CAST(YEAR(GETDATE()) AS VARCHAR(4)) + left(CONVERT(char(20),DATEADD(MONTH,-2,GETDATE()),101),2)SET @CreateLastDBName ='P'+@LastMthDateSET @CreateLast2DBName='P'+@Last2MthDateselect (L2.totalHours-L1.totalHours)totalHours,(L2.labor - L1.labor)labor,(L2.oh - L1.oh) oh,(L2.newlabor- L1.newlabor) newlabor,(L2.newoh- L1.newoh) newoh,L2.departmentfrom @CreateLast2DBName.vw_Apply_Labor_Last2Mnths L2 inner join @CreateLastDBName.vw_Apply_Labor_LastMnth L1 on L2.department = L1.Department
Any thoughts would be appreciated.ThanksLaura
problem dynamically selecting database
Posted - 2010-04-06 : 09:00:53
