TSQL 'Invalid column name' error on value of sproc parameter

Question

here's my code:

DECLARE @SQL varchar(600)

SET @SQL = 
'SELECT     CategoryID, SubCategoryID, ReportedNumber
FROM    tblStatistics
WHERE   UnitCode = ' + @unitCode +
' AND   FiscYear = ' + @currYEAR

EXEC (@SQL)

When i run this sproc with unitCode = 'COB' and currYEAR = '10', i get the following error:

Invalid column name 'COB'.

Does anyone know why?

thx!

Answer 1

You need to put quotes around the values in the SQL:

'SELECT     CategoryID, SubCategoryID, ReportedNumber
FROM    tblStatistics
WHERE   UnitCode = ''' + @unitCode +
''' AND   FiscYear = ''' + @currYEAR + ''''

Answer 2

That's a nice SQL injection vulnerability there.

Start by rewriting it this way, using bind parameters:

DECLARE @SQL nvarchar(4000)

SET @SQL =
    'SELECT CategoryID, SubCategoryID, ReportedNumber ' +
    'FROM tblStatistics ' +
    'WHERE UnitCode = @UnitCode ' +
    'AND FiscYear = @CurrYear'

EXEC sp_executesql
    @SQL,
    '@UnitCode varchar(10), @CurrYear int',
    @UnitCode = 'COB',
    @FiscYear = 10

Answer 3

You don't have quotes inside your quotes - SQL essentially sees

WHERE UnitCode = COB

and COB must not be a column. But why are you building the SQL this way? Why not

SELECT CategoryID, SubCategoryID, ReportedNumber
  FROM tblStatistics
 WHERE UnitCode = @unitCode
   AND FiscYear = @currYear

Answer 4

If we can assume that UnitCode is a VARCHAR field you'd have to add quotes around the @unitcode variable.

DECLARE @SQL varchar(600) 

SET @SQL =  
'SELECT     CategoryID, SubCategoryID, ReportedNumber 
 FROM    tblStatistics 
 WHERE   UnitCode = ''' + @unitCode + ''''
' AND   FiscYear = ' + @currYEAR 

EXEC (@SQL)