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 ;
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?