存储过程1：
- SQL code
createPROCEDURE [dbo].[StorSaleReportDateSet] -- Add the parameters for the stored procedure here @StartTime dateTime, @EndTime dateTime, @flag int = 0 output AS BEGINSELECTproduct.productCode,product.productName,max(product.unitName) as unitName,SUM(proStorBill.quantity) as StorQuantity,SUM(proSaleBill.quantity) as SaleQuantity,AVG(product.storQuantity) as remainQuantityfrom product left join proStorBill on product.productName=proStorBill.productName and proStorBill.storDate is not null and proStorBill.storDate<@EndTime and proStorBill.storDate>@StartTime left join proSaleBill on product.productName=proSaleBill.productName and proSaleBill.saleDate is not null and proSaleBill.saleDate<@EndTime and proSaleBill.saleDate>@StartTime where (proStorBill.storDate<@EndTime and proStorBill.storDate>@StartTime) or (proSaleBill.saleDate<@EndTime and proSaleBill.saleDate>@StartTime) group by product.productName,product.productCode if (@@ROWCOUNT>0) Set @flag = 1 else Set @flag = 0END
执行：
- SQL code
exec [StorSaleReportDateSet] '2012/5/27 0:00:00','2012/6/30 0:00:00'
结果正确：
第二个存储过程：
- SQL code
create PROCEDURE [dbo].[receivableReportDateSet] -- Add the parameters for the stored procedure here @StartTime dateTime, @EndTime dateTime, @flag int = 0 output AS BEGINSELECTbusiPart.busiPartCode,busiPart.busiPartName,SUM(proSaleBill.totalPrice) as totalPrice,SUM(receiptBill.realReciAmount) as realReciAmount,AVG(busiPart.creditUsed) as creditUsedfrom busiPart left join proSaleBill on busiPart.busiPartName=proSaleBill.BusiName and proSaleBill.saleDate is not null and proSaleBill.saleDate<@EndTime and proSaleBill.saleDate>@StartTime left join receiptBill on busiPart.busiPartName=receiptBill.receDate and receiptBill.receDate is not null and receiptBill.receDate<@EndTime and receiptBill.receDate>@StartTime where (proSaleBill.saleDate<@EndTime and proSaleBill.saleDate>@StartTime) or (receiptBill.receDate<@EndTime and receiptBill.receDate>@StartTime) group by busiPart.busiPartName,busiPart.busiPartCode if (@@ROWCOUNT>0) Set @flag = 1 else Set @flag = 0END
执行：
- SQL code
exec [receivableReportDateSet] '2012/5/27 0:00:00','2012/6/30 0:00:00'
错误提示：
存储过程几乎是一样的，只是表换了，传的参数也是一模一样。但是一个执行结果正确，另一个却提示“从字符串转换日期和/或时间时，转换失败”。 小弟实在不解，恳请各位大侠解答，万分感谢
------解决方案--------------------
- SQL code
TRY 修改第二个存储过程CREATE PROCEDURE [dbo].[Receivablereportdateset] -- Add the parameters for the stored procedure here @StartTime DATETIME, @EndTime DATETIME, @flag INT = 0 outputAS BEGIN SELECT busiPart.busiPartCode, busiPart.busiPartName, Sum(proSaleBill.totalPrice) AS totalPrice, Sum(receiptBill.realReciAmount) AS realReciAmount, Avg(busiPart.creditUsed) AS creditUsed FROM busiPart LEFT JOIN proSaleBill ON busiPart.busiPartName = proSaleBill.BusiName AND proSaleBill.saleDate IS NOT NULL AND proSaleBill.saleDate < @EndTime AND proSaleBill.saleDate > @StartTime LEFT JOIN receiptBill ON busiPart.busiPartName = receiptBill.receDate AND receiptBill.receDate IS NOT NULL AND receiptBill.receDate < CONVERT(VARCHAR(32),@EndTime,120) AND receiptBill.receDate > CONVERT(VARCHAR(32),@StartTime,120) WHERE ( proSaleBill.saleDate < @EndTime AND proSaleBill.saleDate > @StartTime ) OR ( receiptBill.receDate < CONVERT(VARCHAR(32),@EndTime,120) AND receiptBill.receDate > CONVERT(VARCHAR(32),@StartTime,120) ) GROUP BY busiPart.busiPartName, busiPart.busiPartCode IF ( @@ROWCOUNT > 0 ) SET @flag = 1 ELSE SET @flag = 0 END