Invalid column name when running script

Question

Hi folks, I am getting these annoying "Invalid column name 2012" and "Invalid column name 2" when running the following script:

declare @Period varchar(3)
declare @GlYear varchar(5)
declare @view varchar(50)
declare @sql varchar(5000)
set @Period=(select GlPeriod from dbo.GenControl)
set @GlYear=(select GlYear  from dbo.GenControl)


set @view ='vw_mimBudget'+@Period


select @sql='


SELECT     dbo.MimGlCodes.Department,dbo.MimGlCodes.Description1 as [Expense Element], SUM(dbo.GenTransaction.EntryValue) AS MTDCost, dbo.[' + @view+ '].Budget
FROM         dbo.MimGlCodes INNER JOIN
                      dbo.GenTransaction ON dbo.MimGlCodes.GlCode = dbo.GenTransaction.GlCode INNER JOIN
                     [' + @view+ '] ON dbo.MimGlCodes.Department = [' + @view+ '].Department AND 
                      dbo.MimGlCodes.Description1 = [' + @view+ '].ExpenseElement
WHERE     (dbo.GenTransaction.GlYear = [' + @GlYear+ ']) AND (dbo.GenTransaction.GlPeriod = [' + @Period+ '])
GROUP BY dbo.MimGlCodes.Department, dbo.MimGlCodes.Description1,dbo.GenTransaction.GlPeriod, [' + @view+ '].Budget'
exec (@sql)

Any idea as to how I can resolve this?

Answer 1

It looks like you are making a mistake in your concatenation, in particular the building of the WHERE clause.

Change the EXEC (@SQL) into a print, you will then see the code and identify the mistake that way.

You are taking the Year and Period and presenting it to the WHERE clause inside square brackets, so SQL Server thinks you are talking about a column, not a literal value.

Try removing the square brackets and supplying the two values in inverted commas (i have used QUOTENAME for this, it is so much easier when concatenating strings):

DECLARE @Period varchar(3)
DECLARE @GlYear varchar(5)
DECLARE @view varchar(50)
DECLARE @sql varchar(5000)
SET @Period = (SELECT   1)
SET @GlYear = (SELECT   2001)

SET @view = 'vw_mimBudget' + @Period

SELECT  @sql = '

SELECT dbo.MimGlCodes.Department,
       dbo.MimGlCodes.Description1 as [Expense Element], 
       SUM(dbo.GenTransaction.EntryValue) AS MTDCost, 
       dbo.' + QUOTENAME(@view) + '.Budget 
FROM dbo.MimGlCodes 
INNER JOIN dbo.GenTransaction ON dbo.MimGlCodes.GlCode = dbo.GenTransaction.GlCode 
INNER JOIN ' + QUOTENAME(@view) + ' ON dbo.MimGlCodes.Department = ' + QUOTENAME(@view) + '.Department AND dbo.MimGlCodes.Description1 = ' + QUOTENAME(@view) + '.ExpenseElement 
WHERE (dbo.GenTransaction.GlYear = ' + QUOTENAME(@GlYear, '''') + ') 
AND (dbo.GenTransaction.GlPeriod = ' + QUOTENAME(@Period, '''') + ') 
GROUP BY dbo.MimGlCodes.Department, 
         dbo.MimGlCodes.Description1,
         dbo.GenTransaction.GlPeriod, 
         ' + QUOTENAME(@view) + '.Budget'
--EXEC (@sql)
PRINT @sql

Invalid column name when running script

Follow this question

Related Questions