Hi All
I receive this error when I execute stored procedure from query page.
This is my stored procedure :
Alter Proc [dbo].[SP_CompPrd]
(
@CompPrd_Temp nvarchar(128),
@Prv_strt_yr smalldatetime,
@Cur_End_Prd smalldatetime
)
As
Declare @MySQL nvarchar(2000);
Declare @MyFdate nvarchar(10);
Declare @MyTdate nvarchar(10);
begin
Set @MySQL = N'Drop table ' + @CompPrd_Temp
exec sp_executesql @MySQL
print @MySQL
---------------------------------------------------
Set @MySQL = N''
Set @MySQL = N'SELECT Memo1, CustomerCategory, ItemCode, CurrDay_Qty, PrvDay_Qty, CurrPrd_Qty, PrvPrd_Qty, CurrYr_Qty, PrvYr_Qty
INTO [' + @CompPrd_Temp + ']
FROM CompPrd_Temp
WHERE Memo1 <> Memo1'
exec sp_executesql @MySQL
print @MySQL
----------------------------------------------------
set @MyFdate = '' + convert(nvarchar(10),@Prv_strt_yr,102)+ ''
Set @MyTdate = '' + convert(nvarchar(10),@Cur_End_Prd,102) + ''
Set @MySQL = N''
Set @MySQL = N'INSERT INTO ' + @CompPrd_Temp + ' (Memo1, CustomerCategory, ItemCode)
SELECT RouteMaster.Memo1, CustomerMaster.CustomerCategory, InvoiceDetail.ItemCode
FROM InvoiceDetail INNER JOIN InvoiceHeader ON InvoiceDetail.TransactionKey = InvoiceHeader.TransactionKey INNER JOIN HITEMGROUP ON InvoiceDetail.ItemCode = HITEMGROUP.HITEMCODE INNER JOIN RouteMaster INNER JOIN CustomerMaster ON RouteMaster.RouteCode = CustomerMaster.RouteCode ON InvoiceHeader.CustomerCode = CustomerMaster.CustomerCode
WHERE (InvoiceHeader.TransactionDate between ' + convert(smalldatetime,@MyFdate,102) + ' AND ' + convert(smalldatetime,@MyTdate,102) + ')
GROUP BY RouteMaster.Memo1, CustomerMaster.CustomerCategory, InvoiceDetail.ItemCode
ORDER BY RouteMaster.Memo1, CustomerMaster.CustomerCategory, InvoiceDetail.ItemCode'
exec sp_executesql @MySQL
print @MySQL
End
Then when execute it like :
exec SP_CompPrd '##CompPrd_Temp160','01-01-2009','07-02-2010'
it return this error for me :
Msg 295, Level 16, State 3, Procedure SP_CompPrd, Line 27
Syntax error converting character string to smalldatetime data type.
What I need that I build logic in ##temp table in memory then later I will fill this data which I get it from the logic to dataset and product the report from dataset
That is my logic .
By the way this logic is run in asp.net behind code page but I try to make in stored procedure because stored procedure is better and safe
I need help ASAP .
Thanks for all