CREATE procedure ct @okdate1 nvarchar(20),@okdate2 nvarchar(20), @receivedate1 nvarchar(20),@receivedate2 nvarchar(20) as declare @sql varchar(3000) set @sql='Select Real_Price, Invoice_Prices ,NotHuiKuan_Prices From(Select 1 id,sum(distinct Promon_Invoice.Invoice_Prices) NotHuiKuan_Prices from Promon_Invoice left join Promon_Invoice_PO on Promon_Invoice_PO.Invoice_ID=Promon_Invoice.ID left join Promon_PO on Promon_PO.ID=Promon_Invoice_PO.PO_ID where Promon_Invoice.OK_Money=0'if(@okdate1 is not null and @okdate2 is not null) begin set @sql=@sql+' and convert(char(10),Confirmed_Completed_Date,120) between '''+@okdate1+''' and '''+@okdate2+'''' end else if(@receivedate1 is not null and @receivedate2 is not null) begin set @sql=@sql+' and convert(char(10),Receive_Date,120) between '''+@receivedate1+''' and '''+@receivedate2+'''' end else set @sql=@sql+' ) c,(Select 1 id,sum(Promon_PO.Real_Price) Real_Price from Promon_PO left join Promon_Invoice_PO on Promon_PO.ID=Promon_Invoice_PO.PO_ID left join Promon_Invoice on Promon_Invoice_PO.Invoice_ID=Promon_Invoice.ID where 1=1' if(@okdate1 is not null and @okdate2 is not null) begin set @sql=@sql+' and convert(char(10),Confirmed_Completed_Date,120) between '''+@okdate1+''' and '''+@okdate2+'''' end else if(@receivedate1 is not null and @receivedate2 is not null) begin set @sql=@sql+' and convert(char(10),Receive_Date,120) between '''+@receivedate1+''' and '''+@receivedate2+'''' end else set @sql=@sql+' ) a, (Select 1 id,sum(distinct Promon_Invoice.Invoice_Prices) Invoice_Prices from Promon_PO left join Promon_Invoice_PO on Promon_PO.ID=Promon_Invoice_PO.PO_ID left join Promon_Invoice on Promon_Invoice_PO.Invoice_ID=Promon_Invoice.ID where 1=1' if(@okdate1 is not null and @okdate2 is not null) begin set @sql=@sql+' and convert(char(10),Confirmed_Completed_Date,120) between '''+@okdate1+''' and '''+@okdate2+'''' end else if(@receivedate1 is not null and @receivedate2 is not null) begin set @sql=@sql+' and convert(char(10),Receive_Date,120) between '''+@receivedate1+''' and '''+@receivedate2+'''' end else set @sql=@sql+' ) b where a.id = b.id and b.id=c.id' exec (@sql) GO
between '+@receivedate1+' and '+@receivedate2+'' 改为: between '''+@receivedate1+''' and '''+@receivedate2+'''' 或者改为 between '+char(39)+@receivedate1+char(39)+' and '+char(39)+@receivedate2+char(39)+''
这种情况一般都是因为少了查询条件少了单引号,char(39)表示单引号
谢谢大家了,现在不报and附近有语法错误了,但是我按照Limpire的写法,又报okdate2附近有语法错误,例如okdate2='2008-05-06',他报'2008-05-06'附近有语法错误,我在他后面拼接的字符传前也加空格了啊,为什么还报错误啊,我改为set @sql=@sql+' and convert(char(10),Confirmed_Completed_Date,120) between '''+@okdate1+''' and '''+@okdate2,报同样的错误。
@receivedate1 nvarchar(20),@receivedate2 nvarchar(20)
as
declare @sql varchar(3000)
set @sql='Select Real_Price, Invoice_Prices ,NotHuiKuan_Prices From(Select 1 id,sum(distinct Promon_Invoice.Invoice_Prices) NotHuiKuan_Prices
from Promon_Invoice left join Promon_Invoice_PO on Promon_Invoice_PO.Invoice_ID=Promon_Invoice.ID
left join Promon_PO on Promon_PO.ID=Promon_Invoice_PO.PO_ID
where Promon_Invoice.OK_Money=0'if(@okdate1 is not null and @okdate2 is not null)
begin
set @sql=@sql+' and convert(char(10),Confirmed_Completed_Date,120) between '''+@okdate1+''' and '''+@okdate2+''''
end
else
if(@receivedate1 is not null and @receivedate2 is not null)
begin
set @sql=@sql+' and convert(char(10),Receive_Date,120) between '''+@receivedate1+''' and '''+@receivedate2+''''
end
else
set @sql=@sql+' ) c,(Select 1 id,sum(Promon_PO.Real_Price) Real_Price
from Promon_PO left join Promon_Invoice_PO on Promon_PO.ID=Promon_Invoice_PO.PO_ID
left join Promon_Invoice on Promon_Invoice_PO.Invoice_ID=Promon_Invoice.ID
where 1=1'
if(@okdate1 is not null and @okdate2 is not null)
begin
set @sql=@sql+' and convert(char(10),Confirmed_Completed_Date,120) between '''+@okdate1+''' and '''+@okdate2+''''
end
else
if(@receivedate1 is not null and @receivedate2 is not null)
begin
set @sql=@sql+' and convert(char(10),Receive_Date,120) between '''+@receivedate1+''' and '''+@receivedate2+''''
end
else
set @sql=@sql+' ) a,
(Select 1 id,sum(distinct Promon_Invoice.Invoice_Prices) Invoice_Prices
from Promon_PO left join Promon_Invoice_PO on Promon_PO.ID=Promon_Invoice_PO.PO_ID
left join Promon_Invoice on Promon_Invoice_PO.Invoice_ID=Promon_Invoice.ID
where 1=1'
if(@okdate1 is not null and @okdate2 is not null)
begin
set @sql=@sql+' and convert(char(10),Confirmed_Completed_Date,120) between '''+@okdate1+''' and '''+@okdate2+''''
end
else
if(@receivedate1 is not null and @receivedate2 is not null)
begin
set @sql=@sql+' and convert(char(10),Receive_Date,120) between '''+@receivedate1+''' and '''+@receivedate2+''''
end
else
set @sql=@sql+' ) b
where a.id = b.id and b.id=c.id'
exec (@sql)
GO
改为:
between '''+@receivedate1+''' and '''+@receivedate2+''''
或者改为
between '+char(39)+@receivedate1+char(39)+' and '+char(39)+@receivedate2+char(39)+''