x

Invalid column name when running script

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?
more ▼

asked Aug 01, 2011 at 04:27 AM in Default

rmazambara gravatar image

rmazambara
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Aug 01, 2011 at 04:38 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

You are a star, it worked like a charm!!!
Aug 01, 2011 at 05:14 AM rmazambara
Please mark the question as answered, that way everyone knows you've been helped. Thanks!
Aug 01, 2011 at 05:19 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x38

asked: Aug 01, 2011 at 04:27 AM

Seen: 1422 times

Last Updated: Aug 01, 2011 at 04:39 AM