Hi,

I have created my dynamic procedure in which values are passed at the run time and on that basis query is executed. But i am getting error.

Please help me out.
Error :>
ERROR [42884] [IBM][DB2/NT] SQL0440N No authorized routine named "||" of type "FUNCTION" having compatible arguments was found. LINE NUMBER=19. SQLSTATE=42884

++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++
CREATE PROCEDURE DB2ADMIN.Dup_Data_1 (
IN name_business_in varchar(100),
IN corp_id_in integer )
P1: BEGIN
DECLARE chk_val INT DEFAULT 0;
DECLARE v_name_business varchar(100);
DECLARE v_corp_id integer;
DECLARE v_stmt varchar(2000);

DECLARE sr CURSOR WITH RETURN TO CLIENT FOR s1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET chk_val = 1;


SET v_stmt = ' n.NAME_BUSINESS,
c.CORP_ID
FROM
CLI_CLIENT c
JOIN CLI_CLNT_NAME_V n ON c.CLIENT_ID = n.CLIENT_ID WHERE ';

IF name_business_in is not null then
set v_stmt = v_stmt || 'name_business = ' || '''' || name_business_in || '''';
END IF;
IF corp_id_in is not null then
set v_stmt = v_stmt || 'corp_id = ' || '''' || corp_id_in || '''';
END IF;

PREPARE s1 FROM v_stmt;
open sr;
FETCH sr INTO
v_client_id,v_name_business,v_corp_id;

IF chk_val = 1 THEN
INSERT INTO cli_clnt_duplicate_test
VALUES(v_name_business,v_corp_id);
END IF;

CLOSE sr;
END P1
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++

I already answered that via email: concatenating strings with non-strings is not supported. (SQL is strongly typed, after all.) One has to convert the integer CORP_ID_IN to a string first.

Yes i got that....I had put it here and then i mailed you....I am facing new error now.....

ERROR [42802] [IBM][DB2/NT] SQL0117N The number of values assigned is not the same as the number of specified or implied columns. LINE NUMBER=30. SQLSTATE=42802

Please help me out....

I think it's this line, that is in error:

INSERT INTO cli_clnt_duplicate_test
VALUES(v_name_business,v_corp_id);

how many columns does that table have ?

Infact the table has more columns than i am inserting and none of the columns is not null so that i can insert data in as many columns as i want.

What should i do.....

you have to specify the column names, to let DB2 know which of the columns should be filled with data:

INSERT INTO cli_clnt_duplicate_test ( column1, column2 )
VALUES ( value1 , value2 ) ;

Thank you its working now.....It was very silly mistake which i should had identified. Any way thanks a lot.