查询以在存储过程中允许null或字符串值 [英] Query to allow null or string value in Stored procedure

问题描述

这是我的查询，现在我想用这个查询创建一个SP。我想我在这里错过了世界上最简单的东西，但不确定它是什么。我在这里有参数PRMLOC，PRMICITEM作为输入参数。

Here is my Query, Now I wanted to create a SP with this query. I think I am missing the world's simplest thing in here but not sure of what it is. I have parameters here PRMLOC, PRMICITEM as input parameters.

SELECT T2 . ICITEM , T2 . BABLOC ,
CHAR ( INTEGER ( ICWDTH ) * 10000 +
INTEGER ( ICASRA ) * 100 + INTEGER ( ICTRIM ) ) TIRE_SIZE
, D . UNITSPER
, E . NBRRCKBINB , F . ICDSC1 , F . ICDSC2 , T2 . TQOH , T2 . TQCM ,
CASE WHEN D . UNITSPER <> 0 THEN
CEIL ( INTEGER ( ( T2 . TQOH ) ) / D . UNITSPER )
ELSE INTEGER ( 0 ) END
NBR_USED_RACKS
, CASE WHEN D . UNITSPER <> 0 THEN
E . NBRRCKBINB - ( CEIL ( INTEGER ( ( T2 . TQOH ) ) / D . UNITSPER ) )
ELSE INTEGER ( 0 ) END
NBR_OPEN_RACKS ,
( E . NBRRCKBINB * D . UNITSPER ) - T2 . TQOH
OPEN_UNITS
FROM (
SELECT A . BACMP , A . BALOC , MIN ( A . BAITEM ) ICITEM
, A . BABLOC , INTEGER ( SUM ( A . BAQOH ) ) TQOH ,
INTEGER ( SUM ( A . BAQCM ) ) TQCM
FROM TESTDATA . VINBINI A WHERE A . BALOC = PRMLOC AND 1 = A . BACMP AND
A . BAQOH - A . BAQCM > 0 GROUP BY A . BACMP , A . BALOC , A . BABLOC HAVING
MIN ( A . BAITEM ) = MAX ( A . BAITEM )
AND SUM ( A . BAQOH - A . BAQCM ) > 0
) AS T2 , TESTDATA . PALITMLOC B , PALBINPF E
, VINITEM F , PALLITEMPF D
WHERE T2 . BACMP = B . TACOMP AND T2 . ICITEM = B . ICITEM
AND T2 . BALOC = B . IALOC AND T2 . ICITEM = F . ICITEM
AND T2 . ICITEM = D . ICITEM
AND B . PALLETID = D . PALLETID
AND E . TACOMP = T2 . BACMP
AND E . IALOC = T2 . BALOC
AND E . IMBLOC = T2 . BABLOC
AND  ICITEM LIKE 'PRMITEM%'
 ORDER BY 1 , 2 ;

现在我希望这个查询运行/显示结果如果我将PRMICTEM传递为null / Nothing，它应该显示所有结果。但如果我通过PRMICITEM它应该只显示如下特定的一个



ICITEM ICDSC1 BABLOC

1 AB

1 BC

2 AB

3 AB

3 BC

4 DA



现在，如果我将null / nothing / empty作为PRMICITEM传递，那应该是结果。

如果我通过PRMICITEM ='3'那么它应该只返回记录3





你能来吗？请给我正确的语法只为这个参数？

Now I want this Query to Run / show results if I pass PRMICTEM as null/ Nothing, it should show all the results. But If I pass PRMICITEM it should only show that particular one like below

ICITEM ICDSC1 BABLOC
1 AB
1 BC
2 AB
3 AB
3 BC
4 DA

Now that should be the result if I pass null/ nothing/empty as PRMICITEM.
If I pass PRMICITEM = '3' then it should only return records with 3


Can you please give me with correct syntax just for this parameter alone?

推荐答案

我想你想摆脱

和ICITEM LIKE'PRMITEM ％'



并将其替换为

AND（ICITEM = @PRMICITEM或@PRMICITEM为空）

I think you want to get rid of
AND ICITEM LIKE 'PRMITEM%'

and replace it with
AND (ICITEM = @PRMICITEM OR @PRMICITEM IS NULL)

由于你使用参数的方式，你可能正在构建一些动态SQL - 如果没有，那么本文应该有帮助在存储过程中构建动态SQL [ ^ ]



你做联接的方式是不整洁的 - 请参阅旧的连接语法vs新 [ ^ ]



您可以使用 ISNULL SQL中的[ ^ ] NULLIF [ ^ ]例如

Presumably you are building some dynamic SQL due to the way you are using the parameter - if not then this article should help Building Dynamic SQL In a Stored Procedure[^]

The way you are doing your joins is untidy - see Old join syntax vs New[^]

You can utilise the ISNULL[^] in SQL (NULLIF[^] in DB2) e.g.

WHERE ICITEM LIKE ISNULL('PRMITEM%', '%')

这篇关于查询以在存储过程中允许null或字符串值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！