我对下面的存储过程代码有问题，它运行顺利，但当我试图执行存储过程时，它抛出了以下错误：
消息241，级别16，状态1，程序ACC_ARAP_DOC_LIST，第27行
从字符串转换日期和/或时间时转换失败。
我猜是@DATE_FROM和@DATE_TO？从外部声明为DATETIME格式。在执行存储过程时，我尝试了2001-01-01和01-01-2001格式。两人都犯了同样的错误。我使用的是SQL Server 2014 Management Studio。
那么，有人知道问题出在哪里吗？
ALTER PROCEDURE [dbo].[ACC_ARAP_DOC_LIST]
@DATE_FROM DATETIME,
@DATE_TO DATETIME,
@DOC_TYPE_GROUP CHAR(20)='DIV',
@FILTER_CONDITION NVARCHAR(4000)='',
@RESULT_MODE NVARCHAR(30)= NULL --'DOC_SUM'
AS
BEGIN
DECLARE @SQL NVARCHAR(4000) --Select
DECLARE @SQL2 NVARCHAR(4000)
DECLARE @WHERE NVARCHAR(4000)
DECLARE @PARAMETER NVARCHAR(1000)
DECLARE @GROUP_BY NVARCHAR(4000)
DECLARE @GROUP_ORDER_BY NVARCHAR(4000)
DECLARE @ORDER_BY NVARCHAR(4000)
DECLARE @SQL_SUM_START NVARCHAR(4000)
DECLARE @SQL_SUM_END NVARCHAR(4000)
SET @SQL = 'SELECT * FROM (SELECT j1.journal_no, j1.acc_date, j1.partner_code, j1.term_code, j1.due_date, j1.ref_no1, j1.ref_no2,
j1.description,j1.sales_person,j1.pay_remark ,j1.amount,j1.acc_amount,j1.currency, j1.match_acc_amount, p1.name as partner_name
FROM acc_journal j1
INNER JOIN acc_journal_groupcfg g1 ON g1.group_type=''LST'' AND g1.doc_type_group=' + @DOC_TYPE_GROUP + ' AND g1.doc_type=j1.doc_type
LEFT OUTER JOIN partner p1 ON j1.partner_code=p1.partner_code
LEFT OUTER JOIN partner_acc p2 ON p1.partner_id=p2.partner_id) as sql1'
SET @WHERE = ' WHERE acc_date>=' + @DATE_FROM +' AND acc_date<=' + @DATE_TO + ' ' +
(CASE WHEN ISNULL(@filter_condition,'')<>''
THEN 'AND '+@filter_condition ELSE '' END)
SET @ORDER_BY = ' ORDER BY journal_no'
SET @PARAMETER = '@DATE_FROM DATETIME,@DATE_TO DATETIME,@RESULT_MODE NVARCHAR(30)=''DOC_SUM'',@DOC_TYPE_GROUP CHAR(20)'
SET @SQL = @SQL + @WHERE + @ORDER_BY
IF @RESULT_MODE = 'DOC_SUM'
BEGIN
SET @SQL = 'SELECT * FROM (SELECT j1.journal_no, j1.acc_date, j1.partner_code as Code, j1.term_code, j1.due_date, j1.ref_no1, j1.ref_no2,
j1.description,j1.sales_person,j1.pay_remark ,j1.amount,j1.acc_amount,j1.currency, j1.match_acc_amount, p1.name as Name
FROM acc_journal j1
INNER JOIN acc_journal_groupcfg g1 ON g1.group_type=''LST'' AND g1.doc_type_group=' +@DOC_TYPE_GROUP +'AND g1.doc_type=j1.doc_type
LEFT OUTER JOIN partner p1 ON j1.partner_code=p1.partner_code
LEFT OUTER JOIN partner_acc p2 ON p1.partner_id=p2.partner_id) as sql1'
SET @SQL_SUM_START= 'SELECT Code, Name, COUNT(*) as Count, SUM(acc_amount) as Total_Amount FROM ('
SET @SQL_SUM_END= ') as SQLsum'
SET @GROUP_BY = ' GROUP BY Code, Name'
SET @GROUP_ORDER_BY = ' ORDER BY Code, Name'
SET @SQL2 = @SQL_SUM_START + @SQL +@SQL_SUM_END + @GROUP_BY + @GROUP_ORDER_BY
END
SET @SQL = 'SELECT * FROM (SELECT j1.journal_no, j1.acc_date, j1.partner_code, j1.term_code, j1.due_date, j1.ref_no1, j1.ref_no2,
j1.description,j1.sales_person,j1.pay_remark ,j1.amount,j1.acc_amount,j1.currency, j1.match_acc_amount, p1.name as partner_name
FROM acc_journal j1
INNER JOIN acc_journal_groupcfg g1 ON g1.group_type=''LST'' AND g1.doc_type_group=' + @DOC_TYPE_GROUP +'AND g1.doc_type=j1.doc_type
LEFT OUTER JOIN partner p1 ON j1.partner_code=p1.partner_code
LEFT OUTER JOIN partner_acc p2 ON p1.partner_id=p2.partner_id) as sql1'
SET @SQL = @SQL + @WHERE + @ORDER_BY
EXEC sp_executesql @SQL, @SQL2, @PARAMETER,
@DOC_TYPE_GROUP=@DOC_TYPE_GROUP,
@DATE_FROM=@DATE_FROM,
@DATE_TO=@DATE_TO
END
您需要将DATETIME参数转换为合适的字符串，以便将它们构建到动态SQL字符串中。例如，代替：
...
SET @WHERE = ' WHERE acc_date>=' + @DATE_FROM +' AND acc_date<=' + @DATE_TO + ' ' +...
使用(注意额外的引号(
...
SET @WHERE = ' WHERE acc_date>=''' + CONVERT(nvarchar(30), @DATE_FROM, 120)
+ ''' AND acc_date<=''' + CONVERT(nvarchar(30), @DATE_TO, 120) + ''' ' + ...
此外，您需要在构建的字符串中引用@DOC_TYPE_GROUP参数，如下所示(再次注意额外的引号(：
...AND g1.doc_type_group=''' + @DOC_TYPE_GROUP + ''' AND g1.doc_type=...
我建议您使用PRINT @SQL而不是SP_EXEC SQL @SQL来帮助您进行调试。
在您的存储过程中，应该连接两个变量@DATE_FROM和@DATE_TO。必须转换为varchar。像这个
SET @WHERE = ' WHERE acc_date>=' +convert(varchar(25), @DATE_FROM )+' AND acc_date<=' +convert(varchar(25), @DATE_TO) + ' ' +
删除此代码
(CASE WHEN ISNULL(@filter_condition,'')<>''
THEN 'AND '+@filter_condition ELSE '' END)
更换
if(isnull(@FILTER_CONDITION,'')!='')
SET @WHERE=@WHERE+'AND '+@filter_condition
您需要将@date_from和@date_to的值转换为NVARCHAR。之后你需要在它周围设置括号。
替换此：
SET @WHERE = ' WHERE acc_date>=' + @DATE_FROM +' AND acc_date<=' + @DATE_TO + '
有了这个：
SET @WHERE = ' WHERE acc_date>=''' + CONVERT(nvarchar(max),@DATE_FROM) +''' AND acc_date<=''' + CONVERT(nvarchar(max),@DATE_TO) + '''
顺便说一句，你也应该提高你的@sql值，以避免一些截止误差。@sql使用nvarchar(4000)。请尝试使用nvarchar(max)。如果您的SQL可能会变长，它可能会避免错误。