请问在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
@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
解决方案 »
- 如何设置contextPath的值
- Linux下部署web项目的编码问题
- 求救大侠jsp问题!!
- 关于hibernate的hsql
- windows 2003部署web,80端口不可用的问题
- 如何在jsp页面上处理由Bean中得到的Resultset?
- 请求高人指点struts中ActionResources_zh_CN.properties 文件问题????????????
- 安全!!大家在安全这方面是如何做的????
- 在一个frame的链接所制定的htm文件,要显示在另一个frame里面,请问怎么实现?
- 关于birt报表结合web 项目的问题
- pos打印机无法自定义纸张大小,只能使用默认的58mm*297mm
- test
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProcInventory]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ProcInventory]
go
create procedure ProcInventory
@GoodsCode nvarchar(50),
@GoodName nvarchar(50),
@BeginDate nvarchar(50),
@EndDate nvarchar(50)
as
begin
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))
/*这里你也可以根据情况自己定义条件 最好不要直接null,在程序里面判断null 赋个特殊值进来做判断,或者在存储过程里面做判断也行。比如if(@GoodsCode is null) set @GoodsCode = '0' 然后在后面直接判断是否为'0',做相应处理。 */
if (@GoodsCode is null and @GoodName is null and @BeginDate is null and @EndDate is null)
begin
set @sql = 'select 1' --这里改成你要的查询语句
end
else
begin
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'
end--exec(@sql)
print @sql
end
go
exec ProcInventory 1,1,1,1
go
exec ProcInventory null,null,null,null
/*
11
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='1' and TbShin.EnterDate between '1'
and '1' and TbShGoods.GoodsCode='1'
select 1
*/