我要做的是一个根据时间查询全部车辆或者某辆车的报表,先写存储过程:
ALTER PROCEDURE [dbo].[CarMoney]
--查询日期
@begin nvarchar(80)=null,
@end nvarchar(80)=null,
--车号
@CarId nvarchar(80)=null
AS
declare @sql nvarchar(4000)
--不指定时间范围
if(@begin is null and @end is null)
Begin
--某个车辆的统计
if(@CarId is not null)
Begin
SET @Sql = ' select c.CarId , isnull(m.Account , 0) 加油费用总计 , '+
' isnull(n.Account , 0) 年检费用合计 from T_Car_base c ' +
' inner join ' +
' (select CarId , sum(Account) Account from T_Car_Oil '+
' where CarId=''' + @CarId +''' group by CarId) m ' +
' on c.CarId = m.CarId ' +
' left join ' +
' (select CarId , sum(CheckMoney) Account from T_Car_YearCheck ' +
' where CarId=''' + @CarId +''' group by CarId) n ' +
' on c.CarId = n.CarId '
End
--全部车辆的统计
else if((@CarId is null) or (@CarId = 'null'))
Begin
SET @Sql = ' select c.CarId , isnull(m.Account , 0) 加油费用总计 , '+
' isnull(n.Account , 0) 年检费用合计 from T_Car_base c ' +
' left join ' +
' (select CarId , sum(Account) Account from T_Car_Oil '+
' group by CarId) m ' +
' on c.CarId = m.CarId ' +
' left join ' +
' (select CarId , sum(CheckMoney) Account from T_Car_YearCheck ' +
' group by CarId) n ' +
' on c.CarId = n.CarId '
End
End
--指定时间范围
else if((@begin is not null) and (@end is not null))
Begin
--某个车辆的统计
if(@CarId is not null)
Begin
SET @Sql = ' select c.CarId , isnull(m.Account , 0) 加油费用总计 , '+
' isnull(n.Account , 0) 年检费用合计 from T_Car_base c ' +
' inner join ' +
' (select CarId , sum(Account) Account from T_Car_Oil '+
' where CarId=''' + @CarId + ''' and (Oildate between ''' + @begin +'''and ''' + @end +''') group by CarId) m ' +
' on c.CarId = m.CarId ' +
' left join ' +
' (select CarId , sum(CheckMoney) Account from T_Car_YearCheck ' +
' where CarId=''' + @CarId +''' group by CarId) n ' +
' on c.CarId = n.CarId '
End
--全部车辆的统计
--这里出错,如果我给CarId传入值是null,执行会转向上一个分支,因为数据类型的原因,就会出错!!!!!如果我传入的值是SQL默认的<NULL>,结果正常。不知道这种思路是否正确。 --会执行这样的结果:
--select c.CarId , isnull(m.Account , 0) 加油费用总计 , isnull(n.Account , 0) 年检费用合计 from T_Car_base c inner join (select CarId , sum(Account) Account from T_Car_Oil where CarId='null' and (Oildate between '2002-1-1'and '2009-12-1') group by CarId) m on c.CarId = m.CarId left join (select CarId , sum(CheckMoney) Account from T_Car_YearCheck where CarId='null' group by CarId) n on c.CarId = n.CarId
--将字符串转换为 uniqueidentifier 时失败。
else if((@CarId is null))
Begin
SET @Sql = ' select c.CarId , isnull(m.Account , 0) 加油费用总计 , '+
' isnull(n.Account , 0) 年检费用合计 from T_Car_base c ' +
' left join ' +
' (select CarId , sum(Account) Account from T_Car_Oil '+
' where Oildate between ''' + @begin +'''and ''' + @end +''' group by CarId) m ' +
' on c.CarId = m.CarId ' +
' left join ' +
' (select CarId , sum(CheckMoney) Account from T_Car_YearCheck ' +
' group by CarId) n ' +
' on c.CarId = n.CarId '
End End
PRINT @sql
exec (@sql)
ALTER PROCEDURE [dbo].[CarMoney]
--查询日期
@begin nvarchar(80)=null,
@end nvarchar(80)=null,
--车号
@CarId nvarchar(80)=null
AS
declare @sql nvarchar(4000)
--不指定时间范围
if(@begin is null and @end is null)
Begin
--某个车辆的统计
if(@CarId is not null)
Begin
SET @Sql = ' select c.CarId , isnull(m.Account , 0) 加油费用总计 , '+
' isnull(n.Account , 0) 年检费用合计 from T_Car_base c ' +
' inner join ' +
' (select CarId , sum(Account) Account from T_Car_Oil '+
' where CarId=''' + @CarId +''' group by CarId) m ' +
' on c.CarId = m.CarId ' +
' left join ' +
' (select CarId , sum(CheckMoney) Account from T_Car_YearCheck ' +
' where CarId=''' + @CarId +''' group by CarId) n ' +
' on c.CarId = n.CarId '
End
--全部车辆的统计
else if((@CarId is null) or (@CarId = 'null'))
Begin
SET @Sql = ' select c.CarId , isnull(m.Account , 0) 加油费用总计 , '+
' isnull(n.Account , 0) 年检费用合计 from T_Car_base c ' +
' left join ' +
' (select CarId , sum(Account) Account from T_Car_Oil '+
' group by CarId) m ' +
' on c.CarId = m.CarId ' +
' left join ' +
' (select CarId , sum(CheckMoney) Account from T_Car_YearCheck ' +
' group by CarId) n ' +
' on c.CarId = n.CarId '
End
End
--指定时间范围
else if((@begin is not null) and (@end is not null))
Begin
--某个车辆的统计
if(@CarId is not null)
Begin
SET @Sql = ' select c.CarId , isnull(m.Account , 0) 加油费用总计 , '+
' isnull(n.Account , 0) 年检费用合计 from T_Car_base c ' +
' inner join ' +
' (select CarId , sum(Account) Account from T_Car_Oil '+
' where CarId=''' + @CarId + ''' and (Oildate between ''' + @begin +'''and ''' + @end +''') group by CarId) m ' +
' on c.CarId = m.CarId ' +
' left join ' +
' (select CarId , sum(CheckMoney) Account from T_Car_YearCheck ' +
' where CarId=''' + @CarId +''' group by CarId) n ' +
' on c.CarId = n.CarId '
End
--全部车辆的统计
--这里出错,如果我给CarId传入值是null,执行会转向上一个分支,因为数据类型的原因,就会出错!!!!!如果我传入的值是SQL默认的<NULL>,结果正常。不知道这种思路是否正确。 --会执行这样的结果:
--select c.CarId , isnull(m.Account , 0) 加油费用总计 , isnull(n.Account , 0) 年检费用合计 from T_Car_base c inner join (select CarId , sum(Account) Account from T_Car_Oil where CarId='null' and (Oildate between '2002-1-1'and '2009-12-1') group by CarId) m on c.CarId = m.CarId left join (select CarId , sum(CheckMoney) Account from T_Car_YearCheck where CarId='null' group by CarId) n on c.CarId = n.CarId
--将字符串转换为 uniqueidentifier 时失败。
else if((@CarId is null))
Begin
SET @Sql = ' select c.CarId , isnull(m.Account , 0) 加油费用总计 , '+
' isnull(n.Account , 0) 年检费用合计 from T_Car_base c ' +
' left join ' +
' (select CarId , sum(Account) Account from T_Car_Oil '+
' where Oildate between ''' + @begin +'''and ''' + @end +''' group by CarId) m ' +
' on c.CarId = m.CarId ' +
' left join ' +
' (select CarId , sum(CheckMoney) Account from T_Car_YearCheck ' +
' group by CarId) n ' +
' on c.CarId = n.CarId '
End End
PRINT @sql
exec (@sql)
实际的搜索条件会传入两方面的信息:
一个是车辆唯一标识编号@CarId,类型是:uniqueidentifier
一个是时间(初始时间@begin和结束时间@end)
搜索条件是这两方面条件的组合,每种条件对应不同的数据源(SQL字符串)
原来的问题是:如果不给CarId传入值,那么搜索条件是:
--全部车辆的统计
else if((@CarId is null)
现在改成:
else if((@CarId is null)or(@CarId = ''))
就好了,想想,是存储过程中,null 和 ''的概念不清楚谢谢 dawugui了。
总结:存储过程中的null是实际的空值,对应传入的''。