-- Tested and working

DECLARE @Statement VARCHAR(1000), @Year SMALLINT, @AgencyCode VARCHAR(5)

SELECT @Year = 2014, @AgencyCode = '57LHS'

SET @Statement = '

SELECT

[Complaint Number] = QNUMBM,

[Complaint Date] = QCMPDM,

[Complaint Reason] = QCRSNM

FROM OPENQUERY(

[AS400_DB2],

''SELECT QNUMBM, QCMPDM, QCRSNM

FROM QMPLNT

WHERE QCMPDM BETWEEN ' + CAST(@Year*10000 AS CHAR(8)) + ' AND ' + CAST((@Year+1)*10000 AS CHAR(8)) + '

AND QAGYCD = ''''' + @AgencyCode + '''''

AND QCSQNM = 1''

)';

--PRINT @Statement -- visual syntax check

IF OBJECT_ID ('tempdb..#Complaints') IS NOT NULL DROP TABLE #Complaints;

CREATE TABLE #Complaints ([Complaint Number] INT, [Complaint Date] INT, [Complaint Reason] VARCHAR(6));

INSERT INTO #Complaints ([Complaint Number], [Complaint Date], [Complaint Reason])

EXEC(@Statement);