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

    Question Unanswered: Stored procedure error

    Hello guys I have to make a stored procedure which would be called like
    Call procedureName(''); OR Call procedureName('OrderByColumn')

    So the passing parameter can be null or a column name. I am having error on calling my procedure

    Code:
    CREATE PROCEDURE TEST.spTest(IN FTEXPRESSION VARCHAR(50))  LANGUAGE SQL DYNAMIC RESULT SETS 1 READS SQL DATA 
    BEGIN   DECLARE CMD VARCHAR ( 50 ) ;   
    DECLARE WHR VARCHAR ( 50 );  
    DECLARE X CURSOR FOR SL ;   
    SET CMD = 'select * from test.Table1 ' ;   
    IF FTEXPRESSION IS NOT NULL   THEN   SET WHR = FTEXPRESSION ;   
    END IF ;   
    SET CMD = CMD || CASE WHEN WHR IS NULL THEN '' ELSE ' ORDER BY ' || WHR END ;    
    PREPARE SL FROM CMD ;   
    OPEN X ;   
    RETURN ;   
    END
    1)When I call it its returning an error
    2) Am I doing the cursor and prepare thing the right way ? Please advice me.

    SQL State: 22001
    Vendor Code: -303
    Message: [SQL0303] Host variable *N not compatible. Cause . . . . . : A FETCH, SELECT, CALL, SET, VALUES INTO, GET DIAGNOSTICS, GET DESCRIPTOR, or SET DESCRIPTOR cannot be performed because the data type of host variable *N is not compatible with the data type of the corresponding list item
    Last edited by asp_crazy_guy; 07-20-10 at 08:47.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    If you don't have the Oracle compatibility mode enabled, an empty string ('') is not the same as NULL.

  3. #3
    Join Date
    Mar 2009
    Posts
    73
    Thanks figured it out, it was a data type problem.

Posting Permissions

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