请问在java 里调用存储过程，显示数据，当传的参数为NULL时，查询所有数据，这个怎处理呢？

0 0

请问在java 里调用存储过程，显示数据，当传的参数为NULL时，查询所有数据，这个怎处理呢？0

请问在java 里调用存储过程，显示数据，当传的参数为NULL时，查询所有数据，这个怎处理呢？

create procedure ProcInventory
@GoodsCode nvarchar(50),
@GoodName nvarchar(50),
@BeginDate nvarchar(50),
@EndDate nvarchar(50)
as
declare @sql varchar(4000)

create table #test(ord varchar(30),GoodsCode nvarchar(20),GoodsName nvarchar(50),Amount nvarchar(50),UnitPrice nvarchar(50),
SumMoney nvarchar(10),Brand nvarchar(50),Unit nvarchar(50))
set @sql= 'insert into #Test(ord ,GoodsCode,GoodsName ,Amount ,UnitPrice ,
SumMoney ,Brand ,Unit )
select ROW_NUMBER() over(order by TbShGoods.GoodsCode) as ord, TbShGoods.GoodsCode,i.GoodName,(i.Amount-isnull(0,o.Amount)) as Amount,i.UnitPrice
     ,(i.Sum-isnull(0,o.Sum)) as Sum ,Brand,Unit
      from tbshindetail i left join tbshoutdetail o on i.GoodsID=o.GoodsID
      inner join TbShin on i.InID=TbShin.InID
          inner join TbShGoods on i.GoodsID=TbShGoods.GoodsID
where   i.GoodName='''+@GoodName+''' and TbShin.EnterDate between '''+@BeginDate+'''
and '''+@EndDate +''' and TbShGoods.GoodsCode='''+@GoodsCode+''''
print '11'

exec(@sql)
print @sql

数据库

2013年11月14日 19:47

QQ1067184821
2
0 0 6

1个答案按时间排序按投票排序

0 0

采纳的答案

未测试，大概思路是定义一个where变量
然后依次检查参数变量，如果参数非null，则where 加上比较表达式。

每次检查一下是否需要加上and运算符。

set @sql= 'insert into #Test(ord ,GoodsCode,GoodsName ,Amount ,UnitPrice , 
SumMoney ,Brand ,Unit ) 
select ROW_NUMBER() over(order by TbShGoods.GoodsCode) as ord,  TbShGoods.GoodsCode,i.GoodName,(i.Amount-isnull(0,o.Amount)) as Amount,i.UnitPrice 
     ,(i.Sum-isnull(0,o.Sum)) as Sum ,Brand,Unit 
      from tbshindetail i left join tbshoutdetail o on i.GoodsID=o.GoodsID 
      inner join TbShin on i.InID=TbShin.InID 
          inner join TbShGoods on i.GoodsID=TbShGoods.GoodsID '
set @wheres = ''
if @GoodName is not null then
  @where = @where +'''+@GoodName+'''
endif

if @GoodName is not null then
  if @where != '' then
    @where = @where +' and '
  endif
  @where = @where +'''+@GoodName+'''
endif
.....

if @where != '' then
  @where = ' where '+@where
endif

exec(@sql+@where)

2013年11月17日 11:56

dwangel
941
0 1 13