关于几个检索条件,随便输入那个就能检索出来。在存储过程实现。where语句用WHERE M_HINBAN.HNBAN > = CASE WHEN @NHBAN_F IS NULL THEN M_HINBAN.HNBAN ELSE @NHBAN_F END
AND M_HINBAN.HNBAN < = CASE WHEN @NHBAN_T IS NULL THEN M_HINBAN.HNBAN ELSE @NHBAN_T END
AND M_HINBAN.NENDO = CASE WHEN @NENDO IS NULL THEN M_HINBAN.NENDO ELSE @NENDO END
AND M_HINBAN.SEASON = CASE WHEN @SEASON IS NULL THEN M_HINBAN.SEASON ELSE @SEASON END
AND M_HINBAN.ITEM = CASE WHEN @ITEM IS NULL THEN M_HINBAN.SEASON ELSE @SEASON END
AND M_HINBAN.SOZAI = CASE WHEN @SOZAI IS NULL THEN M_HINBAN.SOZAI ELSE @SOZAI END
AND M_HINBAN.JODAI >= CASE WHEN @JODAI_F IS NULL THEN M_HINBAN.JODAI ELSE @JODAI_F END
AND M_HINBAN.JODAI <= CASE WHEN @JODAI_T IS NULL THEN M_HINBAN.JODAI ELSE @JODAI_T END
AND M_SHOHIN.IROCD = CASE WHEN @IROCD IS NULL THEN M_SHOHIN.IROCD ELSE @IROCD END
AND M_SHOHIN.SIZE = CASE WHEN @SIZE IS NULL THEN M_SHOHIN.SIZE ELSE @SIZE END发现有的条件为空的,就检索不到数据。
AND M_HINBAN.HNBAN < = CASE WHEN @NHBAN_T IS NULL THEN M_HINBAN.HNBAN ELSE @NHBAN_T END
AND M_HINBAN.NENDO = CASE WHEN @NENDO IS NULL THEN M_HINBAN.NENDO ELSE @NENDO END
AND M_HINBAN.SEASON = CASE WHEN @SEASON IS NULL THEN M_HINBAN.SEASON ELSE @SEASON END
AND M_HINBAN.ITEM = CASE WHEN @ITEM IS NULL THEN M_HINBAN.SEASON ELSE @SEASON END
AND M_HINBAN.SOZAI = CASE WHEN @SOZAI IS NULL THEN M_HINBAN.SOZAI ELSE @SOZAI END
AND M_HINBAN.JODAI >= CASE WHEN @JODAI_F IS NULL THEN M_HINBAN.JODAI ELSE @JODAI_F END
AND M_HINBAN.JODAI <= CASE WHEN @JODAI_T IS NULL THEN M_HINBAN.JODAI ELSE @JODAI_T END
AND M_SHOHIN.IROCD = CASE WHEN @IROCD IS NULL THEN M_SHOHIN.IROCD ELSE @IROCD END
AND M_SHOHIN.SIZE = CASE WHEN @SIZE IS NULL THEN M_SHOHIN.SIZE ELSE @SIZE END发现有的条件为空的,就检索不到数据。
WHERE M_HINBAN.HNBAN > = CASE WHEN @NHBAN_F IS NULL THEN M_HINBAN.HNBAN ELSE @NHBAN_F END
AND M_HINBAN.HNBAN < = CASE WHEN @NHBAN_T IS NULL THEN M_HINBAN.HNBAN ELSE @NHBAN_T END
AND M_HINBAN.NENDO = CASE WHEN @NENDO IS NULL THEN M_HINBAN.NENDO ELSE @NENDO END
AND M_HINBAN.SEASON = CASE WHEN @SEASON IS NULL THEN M_HINBAN.SEASON ELSE @SEASON END
AND M_HINBAN.ITEM = CASE WHEN @ITEM IS NULL THEN M_HINBAN.ITEM ELSE @SEASON END
AND M_HINBAN.SOZAI = CASE WHEN @SOZAI IS NULL THEN M_HINBAN.SOZAI ELSE @SOZAI END
AND M_HINBAN.JODAI >= CASE WHEN @JODAI_F IS NULL THEN M_HINBAN.JODAI ELSE @JODAI_F END
AND M_HINBAN.JODAI <= CASE WHEN @JODAI_T IS NULL THEN M_HINBAN.JODAI ELSE @JODAI_T END
AND M_SHOHIN.IROCD = CASE WHEN @IROCD IS NULL THEN M_SHOHIN.IROCD ELSE @IROCD END
AND M_SHOHIN.SIZE = CASE WHEN @SIZE IS NULL THEN M_SHOHIN.SIZE ELSE @SIZE END你试下是不是你这条:
AND M_HINBAN.ITEM = CASE WHEN @ITEM IS NULL THEN M_HINBAN.SEASON ELSE @SEASON END
当@ITEM 为空时M_HINBAN.ITEM与M_HINBAN.SEASON 值不相等的原因,换成M_HINBAN.ITEM
WHERE M_HINBAN.HNBAN > = CASE WHEN @NHBAN_F IS NULL THEN M_HINBAN.HNBAN ELSE @NHBAN_F END
AND M_HINBAN.HNBAN < = CASE WHEN @NHBAN_T IS NULL THEN M_HINBAN.HNBAN ELSE @NHBAN_T END
AND M_HINBAN.NENDO = CASE WHEN @NENDO IS NULL THEN M_HINBAN.NENDO ELSE @NENDO END
AND M_HINBAN.SEASON = CASE WHEN @SEASON IS NULL THEN M_HINBAN.SEASON ELSE @SEASON END
AND M_HINBAN.ITEM = CASE WHEN @ITEM IS NULL THEN M_HINBAN.ITEM ELSE @SEASON END
AND M_HINBAN.SOZAI = CASE WHEN @SOZAI IS NULL THEN M_HINBAN.SOZAI ELSE @SOZAI END
AND M_HINBAN.JODAI >= CASE WHEN @JODAI_F IS NULL THEN M_HINBAN.JODAI ELSE @JODAI_F END
AND M_HINBAN.JODAI <= CASE WHEN @JODAI_T IS NULL THEN M_HINBAN.JODAI ELSE @JODAI_T END
AND M_SHOHIN.IROCD = CASE WHEN @IROCD IS NULL THEN M_SHOHIN.IROCD ELSE @IROCD END
AND M_SHOHIN.SIZE = CASE WHEN @SIZE IS NULL THEN M_SHOHIN.SIZE ELSE @SIZE END你试下是不是你这条:
AND M_HINBAN.ITEM = CASE WHEN @ITEM IS NULL THEN M_HINBAN.SEASON ELSE @SEASON END
当@ITEM 为空时M_HINBAN.ITEM与M_HINBAN.SEASON 值不相等的原因,换成M_HINBAN.ITEM
--把每个and条件都改为类似
WHERE M_HINBAN.HNBAN > = isnull(@NHBAN_F,M_HINBAN.HNBAN)
--然后动态sql拼接。
--把每个and条件都改为类似
WHERE M_HINBAN.HNBAN > = isnull(@NHBAN_F,M_HINBAN.HNBAN)
--然后动态sql拼接。--NND,CSDN今天怎么一直404
测试了WHERE
M_HINBAN.HNBAN > = ISNULL(@NHBAN_F,M_HINBAN.HNBAN)
AND M_HINBAN.HNBAN < = ISNULL(@NHBAN_T,M_HINBAN.HNBAN)
AND M_HINBAN.NENDO = ISNULL(@NENDO,M_HINBAN.NENDO)
AND M_HINBAN.SEASON = ISNULL(@SEASON,M_HINBAN.SEASON)
AND M_HINBAN.ITEM = ISNULL(@ITEM,M_HINBAN.ITEM)
AND M_HINBAN.SOZAI = ISNULL(@SOZAI,M_HINBAN.SOZAI)
AND M_HINBAN.JODAI > = ISNULL(@JODAI_F,M_HINBAN.JODAI)
AND M_HINBAN.JODAI < = ISNULL(@JODAI_T,M_HINBAN.JODAI)
AND M_SHOHIN.IROCD = ISNULL(@IROCD,M_SHOHIN.IROCD)
AND M_SHOHIN.SIZE = ISNULL(@SIZE,M_SHOHIN.SIZE)只要有一个条件为空
搜索结果就为空啊
M_HINBAN.HNBAN > = ISNULL(@NHBAN_F,M_HINBAN.HNBAN)
OR M_HINBAN.HNBAN < = ISNULL(@NHBAN_T,M_HINBAN.HNBAN)OR 之后 这个范围就不起作用了
and M_HINBAN.HNBAN < = ISNULL(@NHBAN_T,M_HINBAN.HNBAN))
or M_HINBAN.NENDO = ISNULL(@NENDO,M_HINBAN.NENDO)
or...
范围都括起来用and
(M_HINBAN.HNBAN > = @NHBAN_F OR @NHBAN_F = '' )
AND (M_HINBAN.HNBAN < = @NHBAN_T OR @NHBAN_T = '' )
AND (M_HINBAN.NENDO = @NENDO OR @NENDO = '')
AND (M_HINBAN.SEASON = @SEASON OR @SEASON = '')
AND (M_HINBAN.ITEM = @ITEM OR @ITEM = '')
AND (M_HINBAN.SOZAI = @SOZAI OR @SOZAI = '')
AND (M_HINBAN.JODAI > = @JODAI_F OR @JODAI_F = 0)
AND (M_HINBAN.JODAI < = @JODAI_T OR @JODAI_T = 0)
AND (M_SHOHIN.IROCD = @IROCD OR @IROCD = '')
AND (M_SHOHIN.SIZE = @SIZE OR @SIZE = '')这段代码就能实现了