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

Question

请问在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

Answer 1

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

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

[code="java"]
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)
[/code]