TSQL "Invalid column name" error on sproc parameter value

Question

here is 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

This is a good SQL injection vulnerability.

Start by overwriting this way using the binding options:

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 2

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

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

Answer 3

- SQL

WHERE UnitCode = COB

COB . SQL ?

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

Answer 4

, UnitCode VARCHAR, @unitcode.

DECLARE @SQL varchar(600) 

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

EXEC (@SQL)