简略的存储过程当时间参数为NULL 的时候获得不到数据

简单的存储过程当时间参数为NULL 的时候获得不到数据！
---当开始时间有日期值，结束时间EndDate 为NULL 或者空的时候
执行存储过程没有值 数据为NULL

SQL code

DECLARE    @return_value intEXEC    @return_value = [dbo].[P_SaleOrderRtnMonth]        @BeginDate = '2012-7-1',        @EndDate = ''SELECT    'Return Value' = @return_valueGO-- 在BeginDate 和EndDate  都为NULL          的时候 数据可以获取 数据有值  正确-- 在BeginDate 为NULL或空时，EndDate有日期   的时候 数据可以获取 数据有值 正确---当结束时间EndDate 为NULL 或者空的时候  执行存储过程没有值  数据为NULL  ALTER procedure [dbo].[P_SaleOrderRtnMonth]--订单销售退货报表(  @BeginDate datetime,--开始日期  @EndDate datetime --结束日期)as     begin         select a.ShopId,--店铺ID        isnull(s.ShopName,0) ShopName,--店铺名称                          a.SaleMonth,--月份        ISNULL(A.OrderMoney,0.00)OrderMoney,--订单金额        ISNULL(A.ShouldPay,0.00) ShouldPay,--应付金额        ISNULL(PayedMoney,0.00)PayedMoney,--已付金额         isnull(b.RtnCash,0.00) RtnCash,---实退总金额        isnull(b.DeliveryFee,0.00) DeliveryFee,--退运费        ISNULL(b.RtnOrderCount,0) RtnOrderCount--退货单数量from(select ShopId, convert(nvarchar(7),buytime,120) as SaleMonth,SUM(OrderMoney) OrderMoney,SUM(ShouldPay) ShouldPay,SUM(PayedMoney) PayedMoney  from Sales_Order where (@BeginDate is null or @BeginDate='' or buytime >= @BeginDate)  and (@EndDate is null or @EndDate='' or buytime <= @EndDate) and HasSend=1 and Canceled=0 and Deleted=0   group by ShopId,Convert(nvarchar(7),buytime,120)) aright join(select ShopId,Convert(nvarchar(7),ReceiveTime,120) RtnTime,count(RtnId) RtnOrderCount,sum(DeliveryFee) DeliveryFee,sum(RtnCash)  RtnCash from Sales_RtnOrder where RtnType = 1 and Received=1 and Canceled=0  and  (@BeginDate is null or @BeginDate='' or ReceiveTime >= @BeginDate) and (@BeginDate is null or @BeginDate='' or ReceiveTime <= @EndDate) group by ShopId,Convert(nvarchar(7),ReceiveTime,120))b on b.ShopId = a.ShopId and b.RtnTime = a.SaleMonth left join (select ID,ShopName from Top_Shop) s on s.ID = a.ShopId     end

SQL code

ALTER procedure [dbo].[P_SaleOrderRtnMonth]--订单销售退货报表(  @BeginDate datetime,--开始日期  @EndDate datetime --结束日期)as  if @EndDate is null or convert(varchar(8),@EndDate,112)='19000101'    set @EndDate='9999-12-31';//......后面的语句自已改下，不用@EndDate is null or @EndDate='' or buytime <= @EndDate//butime<=@EndDate就可以了
[解决办法]
SQL code
ALTER procedure [dbo].[P_SaleOrderRtnMonth]--订单销售退货报表(  @BeginDate datetime,--开始日期  @EndDate datetime --结束日期)as     begin         select a.ShopId,--店铺ID        isnull(s.ShopName,0) ShopName,--店铺名称                          a.SaleMonth,--月份        ISNULL(A.OrderMoney,0.00)OrderMoney,--订单金额        ISNULL(A.ShouldPay,0.00) ShouldPay,--应付金额        ISNULL(PayedMoney,0.00)PayedMoney,--已付金额         isnull(b.RtnCash,0.00) RtnCash,---实退总金额        isnull(b.DeliveryFee,0.00) DeliveryFee,--退运费        ISNULL(b.RtnOrderCount,0) RtnOrderCount--退货单数量from(select ShopId, convert(nvarchar(7),buytime,120) as SaleMonth,SUM(OrderMoney) OrderMoney,SUM(ShouldPay) ShouldPay,SUM(PayedMoney) PayedMoney  from Sales_Order where (@BeginDate is null or @BeginDate='' or buytime >= @BeginDate)  and (@EndDate is null or @EndDate='' or buytime <= @EndDate) and HasSend=1 and Canceled=0 and Deleted=0   group by ShopId,Convert(nvarchar(7),buytime,120)) aright join(select ShopId,Convert(nvarchar(7),ReceiveTime,120) RtnTime,count(RtnId) RtnOrderCount,sum(DeliveryFee) DeliveryFee,sum(RtnCash)  RtnCash from Sales_RtnOrder where RtnType = 1 and Received=1 and Canceled=0  and [color=#FF0000] (@BeginDate is null or @BeginDate='' or ReceiveTime >= @BeginDate) and (@BeginDate is null or @BeginDate='' or ReceiveTime <= @EndDate) [/color]group by ShopId,Convert(nvarchar(7),ReceiveTime,120))b on b.ShopId = a.ShopId and b.RtnTime = a.SaleMonth left join (select ID,ShopName from Top_Shop) s on s.ID = a.ShopId         end 
 
[解决办法]
EXEC    @return_value = [dbo].[P_SaleOrderRtnMonth]
       @BeginDate = '2012-7-1',
       @EndDate = ''

SELECT    'Return Value' = @return_value

GO

-- 在BeginDate 和EndDate  都为NULL          的时候 数据可以获取 数据有值  正确
-- 在BeginDate 为NULL或空时，EndDate有日期   的时候 数据可以获取 数据有值 正确

---当结束时间EndDate 为NULL 或者空的时候  执行存储过程没有值  数据为NULL 

ALTER procedure [dbo].[P_SaleOrderRtnMonth]--订单销售退货报表
(
 @BeginDate datetime,--开始日期
 @EndDate datetime --结束日期
)
as
  
  begin
        select a.ShopId,--店铺ID
       isnull(s.ShopName,0) ShopName,--店铺名称
                         a.SaleMonth,--月份
       ISNULL(A.OrderMoney,0.00)OrderMoney,--订单金额
       ISNULL(A.ShouldPay,0.00) ShouldPay,--应付金额
       ISNULL(PayedMoney,0.00)PayedMoney,--已付金额  
       isnull(b.RtnCash,0.00) RtnCash,---实退总金额
       isnull(b.DeliveryFee,0.00) DeliveryFee,--退运费
       ISNULL(b.RtnOrderCount,0) RtnOrderCount--退货单数量

from
(select ShopId, convert(nvarchar(7),buytime,120) as SaleMonth,SUM(OrderMoney) OrderMoney,SUM(ShouldPay) ShouldPay,SUM(PayedMoney) PayedMoney  
  from Sales_Order  
where (@BeginDate is null or @BeginDate='' or buytime >= @BeginDate)  
  and (@EndDate is null or @EndDate='' or buytime <= @EndDate) and HasSend=1 and Canceled=0 and Deleted=0  
 group by ShopId,Convert(nvarchar(7),buytime,120)
) a
right join
(select ShopId,Convert(nvarchar(7),ReceiveTime,120) RtnTime,count(RtnId) RtnOrderCount,sum(DeliveryFee) DeliveryFee,sum(RtnCash)  RtnCash from Sales_RtnOrder
  where RtnType = 1 and Received=1 and Canceled=0  and  
 (@BeginDate is null or @BeginDate='' or ReceiveTime >= @BeginDate) and (@BeginDate is null or @BeginDate='' or ReceiveTime <= @EndDate) 
group by ShopId,Convert(nvarchar(7),ReceiveTime,120))b on b.ShopId = a.ShopId and b.RtnTime = a.SaleMonth  
left join  
(select ID,ShopName from Top_Shop) s on s.ID = a.ShopId  
   end

我只能说csdn太贱了