Conversion failed when converting the varchar value

I keep receiving this error:

Conversion failed when converting the varchar value 'INSERT INTO temp_tableZ (Customer_Category_Key, Item_Category_Key, Sales_Rep_Key, time_member_key, Revenue, Fiscal_Year ) select Customer_Category_Key, Item_Category_Key, Sales_Rep_Key, ' to data type int

When running the code below. I believe the problem has something to do with the @.time_member_key in the dynamically created SELECT statement. However, I don't understand the problem or how to fix it.

Can anyone provide some advice?

thank you,

Erik

droptable temp_tableZ
go

createtable temp_tableZ(
Customer_Category_Key INTNULL,
Item_Category_Key INTNULL,
Sales_Rep_Key INTNULL,
Revenue moneyNULL,
time_member_key INTNULL,
Fiscal_Year INTNULL)
go

dropprocedure temp_testA
go

createprocedure temp_testA as
BEGIN
declare @.time_member_key asint
declare @.calendar_date_dt asdatetime
declare @.FY0YTD asMONEY
declare @.sql_string asnvarchar(1024)
set @.calendar_date_dt ='10/1/2005'
while @.calendar_date_dt <'9/30/2006'
BEGIN

select @.time_member_key = time_member_key
from dim_time
where @.calendar_date_dt = dim_time.calendar_date_dt

SET @.sql_string =
'INSERT INTO temp_tableZ'+' ('+
'Customer_Category_Key, '+ 'Item_Category_Key, '+ 'Sales_Rep_Key, '+ 'time_member_key, '+
'Revenue, '+ 'Fiscal_Year '+ ') '+

'select Customer_Category_Key, Item_Category_Key, Sales_Rep_Key, '+ @.time_member_key +', sum(Revenue), 2006'+char(13)+
'from Fact_Sales t1, dim_time t2 '+char(13)+
'where t1.time_member_key = t2.time_member_key'+char(13)+
'and t1.time_member_key < '+ @.time_member_key +char(13)+
'and t2.Fiscal_Year = 2006'+char(13)+
'group by Customer_Category_Key, Item_Category_Key, Sales_Rep_Key, '+ @.time_member_key +char(13)+
'go '

EXECsp_executesql @.sql_string

set @.calendar_date_dt = @.calendar_date_dt + 1

END

END

GO

execute temp_testA

You need to convert @.time_member_key to an NVARCHAR(x), see below.

Read this document on 'Data Type Precedence' to find out why:

http://msdn2.microsoft.com/en-us/library/ms190309.aspx

Chris

SET @.sql_string =

'INSERT INTO temp_tableZ' +' (' +

'Customer_Category_Key, ' + 'Item_Category_Key, ' + 'Sales_Rep_Key, ' + 'time_member_key, ' +

'Revenue, ' + 'Fiscal_Year ' + ') ' +

'select Customer_Category_Key, Item_Category_Key, Sales_Rep_Key, ' + CAST(@.time_member_key AS NVARCHAR(20)) + ', sum(Revenue), 2006' + char(13) +

'from Fact_Sales t1, dim_time t2 ' + char(13) +

'where t1.time_member_key = t2.time_member_key' + char(13) +

'and t1.time_member_key < ' + CAST(@.time_member_key AS NVARCHAR(20)) + char(13) +

'and t2.Fiscal_Year = 2006' + char(13) +

'group by Customer_Category_Key, Item_Category_Key, Sales_Rep_Key, ' + CAST(@.time_member_key AS NVARCHAR(20)) + char(13) +

'go '