If else in Stored procedure

Question

0.00/5 (No votes)

See more:

Hello Experts,

If

PRM_QTY

value is null then it should execure certain part of query other if should check for this condition in sotred procedure
Seems pretty simple but I don't know why this is not working.

SQL

ALTER SPECIFIC PROCEDURE R34FILES.HBINCONRAC REPLACE (IN PRMLOC CHARACTER (2) , IN PRM_ITEM CHARACTER (20) , IN PRM_TRLN CHARACTER (50) , IN PRM_CC CHARACTER (50) , IN PRM_QTY CHARACTER (5) ) MODIFIES SQL DATA CONCURRENT ACCESS RESOLUTION DEFAULT DYNAMIC RESULT SETS 2 OLD SAVEPOINT LEVEL COMMIT ON RETURN NO DISALLOW DEBUG MODE BEGIN

DECLARE CR1 CURSOR WITH RETURN FOR

IF PRM_QTY IS NULL
	BEGIN
	SELECT
	VINBINI . BALOC AS BABLOC , VINBINI . BAITEM AS BAITEM , VINITEM . ICDSC1 AS ICDSC1 ,
	VINITEM . ICDSC2 AS ICDSC2 , VINBINI . BABLOC AS BABLOC ,
	INTEGER ( VINBINI . BAQOH ) AS QOH , INTEGER ( VINBINI . BAQCM ) AS QCM ,
	VINBINI . BACCTU , PALBINPF . PALLETID , PALBINPF . NBRRCKBINB ,
	PALLITEMPF . UNITSPER , VININDEX . I001004
	FROM PRODASP . R34FILES . PALBINPF PALBINPF , PRODASP . R34FILES .  PALLITEMPF PALLITEMPF , PRODASP . R34FILES . VINBINI VINBINI ,PRODASP . R34FILES . VININDEX VININDEX , PRODASP . R34FILES . VINITEM VINITEM WHERE VINBINI . BABLOC 		= PALBINPF . IMBLOC AND VINBINI . BACMP = PALBINPF . TACOMP AND VINBINI . BAITEM = VINITEM . ICITEM AND VINITEM . ICTRLN = VININDEX . I001TRLN AND PALLITEMPF . ICITEM = VINITEM . ICITEM AND PALLITEMPF . PALLETID = PALBINPF . 		PALLETID AND VINBINI . BAITEM =PALLITEMPF . ICITEM AND
	( VINBINI . BAITEM LIKE UPPER ( CONCAT ( TRIM ( PRM_ITEM ) , '%' ) ) OR PRM_ITEM IS NULL ) AND
	( UPPER ( VINITEM . ICTRLN ) LIKE UPPER ( CONCAT ( TRIM ( PRM_TRLN ) , '%' ) ) OR PRM_TRLN IS NULL ) AND
	( VINBINI . BALOC = PRMLOC ) AND ( VINBINI . BAQOH > 0 ) AND ( VININDEX . I001004 LIKE ( CONCAT ( TRIM ( PRM_CC ) , '%' ) ) ORPRM_CC IS NULL )
	ORDER BY BAITEM ASC ;
	END; 
ELSE 
	BEGIN
	SELECT
	VINBINI . BALOC AS BABLOC , VINBINI . BAITEM AS BAITEM , VINITEM . ICDSC1 AS ICDSC1 ,
	VINITEM . ICDSC2 AS ICDSC2 , VINBINI . BABLOC AS BABLOC ,
	INTEGER ( VINBINI . BAQOH ) AS QOH , INTEGER ( VINBINI . BAQCM ) AS QCM ,
	VINBINI . BACCTU , PALBINPF . PALLETID , PALBINPF . NBRRCKBINB ,
	PALLITEMPF . UNITSPER , VININDEX . I001004
	FROM PRODASP . R34FILES . PALBINPF PALBINPF , PRODASP . R34FILES . PALLITEMPF PALLITEMPF , PRODASP . R34FILES . VINBINI VINBINI ,PRODASP . R34FILES . VININDEX VININDEX , PRODASP . R34FILES . VINITEM VINITEM WHERE VINBINI . BABLOC 		= PALBINPF . IMBLOC AND VINBINI . BACMP = PALBINPF . TACOMP AND VINBINI . BAITEM = VINITEM . ICITEM AND VINITEM . ICTRLN = VININDEX . I001TRLN AND PALLITEMPF . ICITEM = VINITEM . ICITEM AND PALLITEMPF . PALLETID = PALBINPF . 		PALLETID AND VINBINI . BAITEM =PALLITEMPF . ICITEM AND
	( VINBINI . BAITEM LIKE UPPER ( CONCAT ( TRIM ( PRM_ITEM ) , '%' ) ) OR PRM_ITEM IS NULL ) AND
	( UPPER ( VINITEM . ICTRLN ) LIKE UPPER ( CONCAT ( TRIM ( PRM_TRLN ) , '%' ) ) OR PRM_TRLN IS NULL ) AND
	( VINBINI . BALOC = PRMLOC ) AND ( VINBINI . BAQOH > 0 ) AND (VINBINI.BAQOH < (INTEGER(PRM_QTY))) AND ( VININDEX . I001004 LIKE ( CONCAT ( TRIM ( PRM_CC ) , '%' ) ) ORPRM_CC IS NULL )
	ORDER BY BAITEM ASC ;
	END;

OPEN CR1 ;
END ;

Or can I completely ignore If else and do something like below

SQL

SELECT
	VINBINI . BALOC AS BABLOC , VINBINI . BAITEM AS BAITEM , VINITEM . ICDSC1 AS ICDSC1 ,
	VINITEM . ICDSC2 AS ICDSC2 , VINBINI . BABLOC AS BABLOC ,
	INTEGER ( VINBINI . BAQOH ) AS QOH , INTEGER ( VINBINI . BAQCM ) AS QCM ,
	VINBINI . BACCTU , PALBINPF . PALLETID , PALBINPF . NBRRCKBINB ,
	PALLITEMPF . UNITSPER , VININDEX . I001004
	FROM PRODASP . R34FILES . PALBINPF PALBINPF , PRODASP . R34FILES . PALLITEMPF PALLITEMPF , PRODASP . R34FILES . VINBINI VINBINI , PRODASP . R34FILES . VININDEX VININDEX , PRODASP . R34FILES . VINITEM VINITEM WHERE VINBINI . BABLOC		= PALBINPF . IMBLOC AND VINBINI . BACMP = PALBINPF . TACOMP AND VINBINI . BAITEM = VINITEM . ICITEM AND VINITEM . ICTRLN = VININDEX . I001TRLN AND PALLITEMPF . ICITEM = VINITEM . ICITEM AND PALLITEMPF . PALLETID = PALBINPF .		PALLETID AND VINBINI . BAITEM = PALLITEMPF . ICITEM AND
	( VINBINI . BAITEM LIKE UPPER ( CONCAT ( TRIM ( PRM_ITEM ) , '%' ) ) OR PRM_ITEM IS NULL ) AND
	( UPPER ( VINITEM . ICTRLN ) LIKE UPPER ( CONCAT ( TRIM ( PRM_TRLN ) , '%' ) ) OR PRM_TRLN IS NULL ) AND
	( VINBINI . BALOC = PRMLOC ) AND ( VINBINI . BAQOH > 0 ) AND ( VINBINI . BAQOH < INTEGER ( PRM_QTY ) OR PRM_QTY IS NULL ) AND ( VININDEX . I001004 LIKE ( CONCAT ( TRIM ( PRM_CC ) , '%' ) ) OR PRM_CC IS NULL )
	ORDER BY BAITEM ASC ;

Can you please help me with this

Posted 26-Oct-15 4:15am

sudevsu

Updated 26-Oct-15 4:48am

Naveen.Sanagasetti

v3

Add a Solution

Comments

ZurdoDev 26-Oct-15 10:23am

I don't do MySql but it looks like you have it right. A real simple test is to comment out the select statement and replace with PRINT statements (if MySql supports that) or simple SELECT 1 in the first if and SELECT 2 in the else. That way you can at least be sure which sql is executing. Then debug from there.

sudevsu 26-Oct-15 10:43am

I may or may not pass the value for PRM_QTY . Meaning. If I don't pass the value it should ignore the comparison. But it throws me an error saying "Data conversion error"

ZurdoDev 26-Oct-15 11:01am

But you don't know which one of the 2 is even running.

sudevsu 26-Oct-15 10:50am

Basically, if I don't pass the value it should ignore that condition

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

George Jonsson · Answer 1 · 2015-10-26T04:40:00

Solution 1

Instead of

SQL

IF PRM_QTY IS NULL

try the built in function

SQL

IF isnull(PRM_QTY) THEN

See MYSQL: ISNULL FUNCTION[^]
And don't forget the THEN in the IF statement.

[UPDATE]
You could also try the ifnull function.

SQL

SET PRM_QTY = ifnull(PRM_QTY, NULL);

or, as you convert to an integer anyway

SQL

DECLARE varQTY INT;
SET varQTY = ifnull(PRM_QTY, 0); -- Or whatever default value you want to have

Posted 26-Oct-15 4:40am

George Jonsson

Updated 26-Oct-15 5:09am

v3

Comments

sudevsu 26-Oct-15 10:45am

Thanks George. I tried it works only if I pass the value. If I don't pass the value it should ignore this clause in where. but it throws me an error data conversion error

George Jonsson 26-Oct-15 10:53am

What do you mean you are not passing the value?
In MySQL there is unfortunately no construct for optional parameters.

sudevsu 26-Oct-15 10:59am

If PRM_QTY is '' then it should ignore the where clause for this condition alone.
It should just check VINBIN.BAQOH > 0
It PRM_QTY is some value '50' then it should give me results like
VINBIN.BAQOH >0 and VINBIN.BINBAOH < INTEGER(50)

George Jonsson 26-Oct-15 11:08am

See my updated solution.
Maybe it will help you forward.