Declare @SalesMonth int
Declare @SalesYear int
DECLARE @TSQL varchar(8000)
Set @SalesMonth = 5
Set @SalesYear = 2009
CREATE TABLE #Tmp ( State varchar(10),
City Varchar(10),
SaleYear Int,
SalesMonth Int,
Sales decimal(16,4))
Select @TSQL=
'Insert into #Tmp
Select L.State, L.City,l.Saleyear,l.Salesmonth,l.Sales
FROM OPENQUERY([Net06],''SELECT L.State, L.City,
year(L.invoicedate) as saleyear,
month(L.invoicedate) as salesmonth,
sum(L.Sales) as sales
FROM Net06.SalesData.dbo.tblSales L
WHERE (month(L.Invoicedate)=CAST('''''+@SalesMonth +''''' as Int) AND year(L.invoicedate) = CAST('''''+@SalesYear +''''' as Int)
AND L.extendedprice <> 0
GROUP BY L.State, L.City,
year(L.invoicedate),
month(L.invoicedate)'') as L'
Exec (@TSQL)
Select * from #tmp
order by 1,2
drop table #tmp
When I run this open query its giving me error saying,
Msg 245, Level 16, State 1, Line 13
Conversion failed when converting the varchar value to data type int
I think the error is while passing the variables into the Invoice Date. If I run the query removing the date varibles and enter them manually it runs fine,
Like where (month(L.invoicedate)=5 and year(L.invoicedate) = 2009)
Can anybody help me?