Something is wrong with a query syntax for bcp command in SQL

I'm building a query for my bcp command in SQL. When executing the statement I have the following error:

Conversion failed when converting the varchar value 'SELECT 'Transaction Unique ID','Transaction Date', 'Person Unique ID' UNION ALL SELECT NULL AS 'Transaction Unique ID', CONVERT(VARCHAR(10),CONVERT(DATETIME,Date,1),101) AS ' Transaction Date', NULL AS 'Person Unique ID' FROM tblChromeRiverInitData WHERE YYYYMM = ' to data type int.

This is the syntax for my bcp command:

DECLARE @query VARCHAR(2000)
DECLARE @bcpCommand VARCHAR(1024)
DECLARE @sharedDevFolder VARCHAR(500)
DECLARE @fileName VARCHAR(200)
DECLARE @environment VARCHAR(5)
DECLARE @customerCode VARCHAR(5)
DECLARE @parserConfig VARCHAR(5)
DECLARE @bucketAssign VARCHAR(10)
DECLARE @dateFormat VARCHAR(15)
DECLARE @input VARCHAR(15)
DECLARE @RC int

SET @sharedDevFolder = '\\REMOTE_SERVER\MyDirectory\'
SET @input = 201507
SET @fileName = 'Transaction-' + 
                @environment + '-' + 
                @customerCode + '-' + 
                @parserConfig + '-' + 
                @bucketAssign + '-' + 
                @dateFormat + '.txt'

SET @query = 
    'SELECT ''Transaction Unique ID'',''Transaction Date'', ''Person Unique ID'' UNION ALL SELECT NULL AS ''Transaction Unique ID'', CONVERT(VARCHAR(10),CONVERT(DATETIME,Date,1),101) AS '' Transaction Date'', NULL AS ''Person Unique ID'' FROM tblChromeRiverInitData WHERE YYYYMM = ' + CAST(@input as VARCHAR(10))

SET @bcpCommand = 'bcp "' + @query + '" queryout "'
set @bcpCommand = @bcpCommand + @sharedDevFolder + @fileName + '" -c  -T -t^| -r\n'

EXEC @RC = master..xp_cmdshell @bcpCommand

I'm not sure what's wrong.

When executed, the following query is printed:

SELECT 'Transaction Unique ID','Transaction Date', 'Person Unique ID' UNION ALL SELECT NULL AS 'Transaction Unique ID', CONVERT(VARCHAR(10),CONVERT(DATETIME,Date,1),101) AS 'Transaction Date', NULL AS 'Person Unique ID' FROM tblChromeRiverInitData WHERE YYYYMM = '201507'

I have been trying to solve it for sometimes already and it's really bugging me.

Can you please explain what I'm doing wrong and if possible provide me with the correct way of doing this?

Solution

You've got a data type mismatch going on. The + @input at the end of your query is the issue.

I'm guessing the column [YYYYMM] is a number? It should be a string/varchar.

So your line could be

declare @input as varchar
set @input = '201505'
set @query = 'SELECT ''Transaction Unique ID'',''Transaction Date''...WHERE YYYYMM = ''' + @input + ''''

if [YYYYMM] is a string, or if it's not and it's numeric

declare @input as int
set @input = 201505
set @query = 'SELECT ''Transaction Unique ID'',''Transaction Date''...WHERE YYYYMM = ' + cast(@input as varchar)