Hi there,

I do have an issue with a cursor with dynamically creating the SQL statement.
The below works okay.

CREATE PROCEDURE TEST_SQL
(IN P_ACCNUM VARCHAR(6),
IN P_CMPNAME VARCHAR(25),
IN P_BOID VARCHAR(3))
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN


-- Declare cursor
DECLARE CURSOR1 CURSOR WITH RETURN FOR
SELECT * FROM CLIENT_FINDER.CLI
WHERE CLIENT_FINDER.CLI.ACCNUM Like P_ACCNUM || '%';

-- Cursor left open for client application
OPEN CURSOR1;
END P1


The following SP fails with message
A database manager error occurred.SQLCODE: -401, SQLSTATE: 42818 - The data types of the operands for the operation "=" are not compatible.. SQLCODE=-401, SQLSTATE=42818, DRIVER=3.57.86

CREATE PROCEDURE BASECLIDATA_SQL ( IN P_ACCNUM VARCHAR(6),
IN P_ACCOFFICENUM VARCHAR(3),
IN P_CMPNAME VARCHAR(25) )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
-- p_AccNum
-- p_AccOfficeNum
-- p_CMPName
------------------------------------------------------------------------
P1: BEGIN

DECLARE V_TABLE_COUNT VARCHAR(200);
DECLARE V_SQL VARCHAR(200);
DECLARE V_STMT STATEMENT;

-- Declare cursor
DECLARE CURSOR1 CURSOR WITH RETURN FOR V_STMT;
SET V_SQL = 'SELECT * FROM CLIENT_FINDER.CLI
WHERE CLIENT_FINDER.CLI.ACCNUM = ' || P_ACCNUM;
PREPARE V_STMT FROM V_SQL;
OPEN CURSOR1;
--FETCH CURSOR1 INTO V_TABLE_COUNT;
-- Cursor left open for client application
END P1

Can anyone help?
Thanks in advance

SET V_SQL = 'SELECT * FROM CLIENT_FINDER.CLI
WHERE CLIENT_FINDER.CLI.ACCNUM Like ' || P_ACCNUM;

I am using the IBM data studio 2.2 FYI

Error returned:
A database manager error occurred.SQLCODE: -440, SQLSTATE: 42884 - No authorized routine named "LIKE" of type "FUNCTION" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=3.57.86

P_ACCNUM has the VARCHAR datatype. Character literals should be enclosed in single quotes.

This must not be specific to SQL.
It is programming basics of string handling and construction of executable code dynamicaly.

Set V_sql = 'select * From Client_finder.cli
Where Client_finder.cli.accnum = ''' || P_accnum || '''';

Thanks guys have figured out now

Hi,

I prepared an sql statement with a like clause. I put the information in a temp table to check the sql.

My test sql string is

delete from test1 where test_id in (select test_id from test2 where test_name like 'P%')

When I send this stmt to prepare and execute to insert in my temp table it work.

However when I ask to prepare and execute this statement ...I get an SQL0007N error.

I would like to know the answer ...to your question as you say it is now resolved.

Any help is well...newbie to DB2.