AND (M.CONTRACT_ID = @P_CONTRACT OR M.CONTRACT_ID IS NULL )
AND (M.REQ_NO =@P_REQ_NO OR M.REQ_NO IS NULL )
AND (CONVERT(VARCHAR(10),C.IN_DATE,111)>=@P_FROM_DATE OR CONVERT(VARCHAR(10),C.IN_DATE,111) IS NULL )
AND (CONVERT(VARCHAR(10),C.IN_DATE,111)<=@P_TO_DATE OR CONVERT(VARCHAR(10),C.IN_DATE,111) IS NULL)试试是不是这样
不可以 结果不对 然后 M.CONTRACT_ID , M.REQ_NO is null 那么在SQL就不出数据!
但是在PL/SQL 是有数据的
AND (M.REQ_NO = @P_REQ_NO OR @P_REQ_NO IS NULL)
AND convert(char(10),C.IN_DATE, 111) >= @P_FROM_DATE OR
@P_FROM_DATE IS NULL)
AND convert(char(10),C.IN_DATE, 111) <= @P_TO_DATE OR
@P_TO_DATE IS NULL) 这样呢?
不可以,要用动态SQL吧 ,更具参数是否为空来 设置条件
SELECT C.IN_DATE,
C.ITEM_NO,
M.XH_NO SEQ,
C.CIQ_NO,
I.ITEM_NAME,
C.GROSS_WEIGHT,
C.IN_QTY,
NVL(DECODE(I.TYPE1,1,C.IN_QTY,0),0) IN_QTY_MAIN,
NVL(DECODE(I.TYPE1,2,C.IN_QTY,0),0) IN_QTY_CHILD,
NVL(DECODE(I.TYPE1,1,C.IN_QTY,0),0) * NVL(M.IN_PRICE,0) MAIN_MONEY,
NVL(DECODE(I.TYPE1,2,C.IN_QTY,0),0) * NVL(M.IN_PRICE,0) CHILD_MONEY,
M.IN_PRICE,
DECODE(I.TYPE1,1,'主料',2,'辅料') TYPE,
C.BAK
FROM NI_CONTRACT_IN_MAIN M, NI_CONTRACT_IN_CHILD C, NI_CONTRACT_ITEM I
WHERE M.CONTRACT_ID = C.CONTRACT_ID
AND M.REQ_NO = C.REQ_NO
AND M.ITEM_NO = I.ITEM_NO
AND C.ITEM_NO = I.ITEM_NO
AND M.ITEM_NO = C.ITEM_NO
AND I.TYPE1 IN ('1','2')
AND (M.CONTRACT_ID = :P_CONTRACT OR :P_CONTRACT IS NULL)
AND (M.REQ_NO = :P_REQ_NO OR :P_REQ_NO IS NULL)
AND (TO_CHAR(C.IN_DATE, 'YYYY/MM/DD') >= :P_FROM_DATE OR
:P_FROM_DATE IS NULL)
AND (TO_CHAR(C.IN_DATE, 'YYYY/MM/DD') <= :P_TO_DATE OR
:P_TO_DATE IS NULL)
ORDER BY C.IN_DATE,C.ITEM_NOSQL 过程
CREATE procedure ni_contract_in_query @contract_id varchar(50),
@req_no varchar(50),
@from_date varchar(20),
@end_date varchar(20)
as
begin
create table #contract_in_query
(in_date datetime,
item_no varchar(50),
xh_no int,
ciq_no varchar(20),
item_name nvarchar(200),
gross_weight decimal(18,4),
in_qty decimal(18,2),
in_qty_main decimal(18,4),
in_qty_child decimal(18,4),
main_money decimal(18,4),
child_money decimal(18,4),
in_price decimal(18,4),
type nvarchar(15),
contract_id varchar(50),
bak nvarchar(500))insert into #contract_in_query
select c.in_date,
c.item_no,
m.xh_no seq,
c.ciq_no,
i.item_name,
c.gross_weight,
c.in_qty,
isnull((case when i.type1 = 1 THEN c.in_qty ELSE 0 END),0) in_qty_main,
isnull((case when i.type1 = 2 THEN c.in_qty ELSE 0 end),0) in_qty_child,
isnull((case when i.type1 = 1 then c.in_qty else 0 end),0) * isnull(m.in_price,0) main_money,
isnull((case when i.type1 = 2 then c.in_qty else 0 end),0) * isnull(m.in_price,0) child_money,
m.in_price,
(case when i.type1 = 1 then '主料' when i.type1 = 2 then '辅料' end) type,
m.contract_id,
c.bak
from ni_contract_in_main m, ni_contract_in_child c, ni_contract_item i
where m.contract_id = c.contract_id
and m.req_no = c.req_no
and m.item_no = i.item_no
and c.item_no = i.item_no
and m.item_no = c.item_no
and i.type1 in ('1','2')
and (m.contract_id = @contract_id or @contract_id is null)
and (m.req_no = @req_no or @req_no is null)
and (convert(varchar,c.in_date,111) >= @from_date or
@from_date is null)
and (convert(varchar,c.in_date,111) <= @end_date or
@end_date is null)
order by c.in_date,c.item_noSELECT in_date,
item_no,
xh_no,
ciq_no,
item_name,
gross_weight,
in_qty,
in_qty_main,
in_qty_child,
main_money,
child_money,
in_price,
type,
contract_id,
bak
FROM #contract_in_query
drop table #contract_in_query
end
GO
语法是没有问题的, 看来是PL/SQL 跟SQL 机制的问题了。PL/SQL 当参数是空的时候可以查找去数据
SQL 查询不出来
@contract_id VARCHAR(50) ,
@req_no VARCHAR(50) ,
@from_date VARCHAR(20) ,
@end_date VARCHAR(20)
AS
BEGIN
CREATE TABLE #contract_in_query
(
in_date DATETIME ,
item_no VARCHAR(50) ,
xh_no INT ,
ciq_no VARCHAR(20) ,
item_name NVARCHAR(200) ,
gross_weight DECIMAL(18, 4) ,
in_qty DECIMAL(18, 2) ,
in_qty_main DECIMAL(18, 4) ,
in_qty_child DECIMAL(18, 4) ,
main_money DECIMAL(18, 4) ,
child_money DECIMAL(18, 4) ,
in_price DECIMAL(18, 4) ,
type NVARCHAR(15) ,
contract_id VARCHAR(50) ,
bak NVARCHAR(500)
) INSERT INTO #contract_in_query
SELECT c.in_date ,
c.item_no ,
m.xh_no seq ,
c.ciq_no ,
i.item_name ,
c.gross_weight ,
c.in_qty ,
ISNULL(( CASE WHEN i.type1 = 1 THEN c.in_qty
ELSE 0
END ), 0) in_qty_main ,
ISNULL(( CASE WHEN i.type1 = 2 THEN c.in_qty
ELSE 0
END ), 0) in_qty_child ,
ISNULL(( CASE WHEN i.type1 = 1 THEN c.in_qty
ELSE 0
END ), 0) * ISNULL(m.in_price, 0) main_money ,
ISNULL(( CASE WHEN i.type1 = 2 THEN c.in_qty
ELSE 0
END ), 0) * ISNULL(m.in_price, 0) child_money ,
m.in_price ,
( CASE WHEN i.type1 = 1 THEN '主料'
WHEN i.type1 = 2 THEN '辅料'
END ) type ,
m.contract_id ,
c.bak
FROM ni_contract_in_main m ,
ni_contract_in_child c ,
ni_contract_item i
WHERE m.contract_id = c.contract_id
AND m.req_no = c.req_no
AND m.item_no = i.item_no
AND c.item_no = i.item_no
AND m.item_no = c.item_no
AND i.type1 IN ( '1', '2' )
AND ( m.contract_id = @contract_id
OR m.contract_id IS NULL
)
AND ( m.req_no = @req_no
OR m.req_no IS NULL
)
AND ( CONVERT(VARCHAR, c.in_date, 111) >= @from_date
OR CONVERT(VARCHAR, c.in_date, 111) IS NULL
)
AND ( CONVERT(VARCHAR, c.in_date, 111) <= @end_date
OR CONVERT(VARCHAR, c.in_date, 111) IS NULL
)
ORDER BY c.in_date ,
c.item_no SELECT in_date ,
item_no ,
xh_no ,
ciq_no ,
item_name ,
gross_weight ,
in_qty ,
in_qty_main ,
in_qty_child ,
main_money ,
child_money ,
in_price ,
type ,
contract_id ,
bak
FROM #contract_in_query
DROP TABLE #contract_in_query
END
GO
嗯,当这个参数为null的时候,那么就筛选!在PL/SQL中 如果这个4个参数都为 null 那么是可以查询出数据来的在SQL 中 参数为空 是没有数据的
AND (M.CONTRACT_ID = @contract_id OR @contract_id IS NULL OR @contract_id = '')
AND (M.REQ_NO = @req_no OR @req_no IS NULL OR @req_no = '')
AND convert(varchar(10), C.IN_DATE, 111) >= @from_date OR @from_date IS NULL OR @from_date = ''
AND convert(varchar(10), C.IN_DATE, 111) <= @end_date OR @end_date IS NULL OR @end_date = ''
但是在SQL 中 '' 是作为 字符处理
但是在SQL 中 '' 是作为 字符处理是这样的,Oracle把空字符串''当做null来处理,但是SQL Server不是,空字符串''就是空字符串'',null is null。
AND (M.CONTRACT_ID = @P_CONTRACT OR NULLIF(@P_CONTRACT,'') IS NULL )
-- 或者
AND (M.CONTRACT_ID = @P_CONTRACT OR ISNULL(@P_CONTRACT,'') = '')