Stored procedure error

asp_crazy_guy · #1 (**permalink**) 07-20-10, 01:03

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