Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2009
    Posts
    73

    Red face Unanswered: Concatenate strings in stored procedure ?

    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

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    if you want to use a single quote within a string, you have to double ist.

    ' ...NOT IN (''MCR'',''MHL'',''MAN'',''AFW'', ''ATW'',''CNZ'',''HNZ'')';




    UPPER(BUSSTYPE)) IS NOT NULL

    this part of the condition may be omitted, as

    UPPER(BUSSTYPE)='NMR'

    is never true if BUSSTYPE is null

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •