Hello fellows,
1) How do I allow single quotes inside my statements in DB2 ISeries V5R4. My procedure throws an error on the single quotes in statement
2) How do I avoid returning null with my stored procedure. How can I improve on my current approach ?
Code:
CREATE PROCEDURE PAKRETST.FTSBCLASS (IN BUSSTYPE CHAR (10))
LANGUAGE SQL DYNAMIC RESULT SETS 1 READS SQL DATA
P1: BEGIN
DECLARE strCmd VARCHAR(500);
DECLARE X CURSOR WITH RETURN TO CALLER FOR SL;
IF (UPPER(BUSSTYPE)) IS NOT NULL AND UPPER(BUSSTYPE)='NMR' THEN
set strCmd = 'Select GENSUBBUSICLASS,gensubbusiclassname from PAKRETST.UWGENSUBBUSICLASS where gensubbusiclass NOT IN ('MCR','MHL','MAN','AFW','ATW','CNZ','HNZ')';
ELSE IF (UPPER(BUSSTYPE)) IS NOT NULL AND UPPER(BUSSTYPE)='MAR' THEN
set strCmd = Select GENSUBBUSICLASS,gensubbusiclassname from PAKRETST.UWGENSUBBUSICLASS where gensubbusiclassname Like 'Marine%' AND GENSUBBUSICLASS NOT IN ('MAN','AFW','ATW');
END IF;
PREPARE SL FROM strCmd;
OPEN X;
SET RESULT SETS CURSOR X;
RETURN;
END
;
SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token MCR was not valid
dBforums
>
Concatenate strings in stored procedure ?
08-16-10, 00:48
Concatenate strings in stored procedure ?
Linear Mode
