Hi
I am trying to paramertize schema name.Below procedure is working fine if I have ABCD--another parametre as varchar.But when I add a parameter as timestamp it throws an error.
CREATE PROCEDURE x.SAMPLE_DYN1
(
IN SCHEMA_NAME VARCHAR(50),
IN ABCD VARCHAR(30)
)
SPECIFIC x.SQL080723153803300
LANGUAGE SQL
NOT DETERMINISTIC
CALLED ON NULL INPUT
MODIFIES SQL DATA
INHERIT SPECIAL REGISTERS
BEGIN
DECLARE ENO INTEGER DEFAULT 0;
DECLARE ENAME CHAR(50);
DECLARE STMT VARCHAR(500);
DECLARE at_end SMALLINT DEFAULT 0;
DECLARE DYN_STMT VARCHAR(500);

DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND-------------Exit handler from cursor loop
BEGIN
set at_end =1;
RESIGNAL;
END;

--DECLARE STMT1 STATEMENT;

SET STMT =' SELECT NAME FROM '||SCHEMA_NAME||'.ZZ_EMPT2 WHERE NO='||ABCD||'' ;


-- EXEC SQL PREPARE S1 FROM TMT;

PREPARE STMT1 FROM STMT;
BEGIN
--EXECUTE STMT1;
DECLARE C1 CURSOR FOR STMT1;
------ Cursor declaration

OPEN C1;
WHILE (at_end = 0) DO
FETCH C1 INTO ENAME;
SET ENO = ENO +1;
SET DYN_STMT = 'INSERT INTO idm_dev.ZZ_EMPT3(NO,NAME) VALUES(?,?)';
PREPARE S1 FROM DYN_STMT;
EXECUTE S1 USING ENO,ENAME;

END WHILE;
END;
COMMIT;
END;

Error:
[IBM][CLI Driver][DB2/LINUXX8664] SQL0440N No authorized routine named "||" of type "FUNCTION" having compatible arguments was found. LINE NUMBER=26. SQLSTATE=42884
Please help

You might have to do a RTRIM on the schema. But why not just issue the set schema command before your SQL Select.

CONCAT only accepts CHAR or VARCHAR als parameter.

other types must be CAST to CHAR before you can CONCAT them.



try:

SET STMT =' SELECT NAME FROM '||SCHEMA_NAME||'.ZZ_EMPT2 WHERE NO='''||CAST(ABCD AS CHAR(26) ) ||'''';

> You need to enquote the concatenated string, so some extra quotes are required.

> there is no need to RTRIM the SCHEMA-NAME. Blancs are allowed between schema and table name

Maybe I am missing something, but variable ABCD appears to be a VARCHAR:

CREATE PROCEDURE x.SAMPLE_DYN1
(
IN SCHEMA_NAME VARCHAR(50),
IN ABCD VARCHAR(30)
)

Thanks for your reply.I converted to char as you had mentioned.But when I execute I get below error

call X.SP_DYN ('X','2008-08-01 18:17:32.000000')
SQL0104N An unexpected token ".32" was found following "S =
2008-08-01-18.17". Expected tokens may include: "SELECTIVITY".
SQLSTATE=42601

please post your complete new CREATE PROCEDURE statement.

Maybe there's just a quote missing

CREATE PROCEDURE X."SP_DYN"
(IN "PROCESS_NAME" VARCHAR(100),
IN "RUN_DT" TIMESTAMP,
IN "SCHEMA_NAME" VARCHAR(50),
OUT "SOURCE_COUNT" INTEGER,
OUT "TARGET_COUNT" INTEGER
)


BEGIN

--======================
--Variable Declarations
--======================


--================================================== ==============
--Cursor Declaration
--================================================== ==============
DECLARE COUNTRY_LOAD CURSOR FOR CURSOR_STMT1;
SET CURSOR_STMT ='SELECT COUNTRY_RTBL.COUNTRY_CD,COUNTRY_RTBL.COUNTRY_NM,A. DM_LINEAGE_ID
FROM '||SCHEMA_NAME||'.COUNTRY_RT COUNTRY_RT,'||SCHEMA_NAME||'.ETL_LINE A
WHERE A.DM_LINEAGE_CREATE_TS = '||CAST('2008-08-01 18:17:32.000000' AS CHAR(26) ) ||' AND A.PROCESS_NM = '||PROCESS_NAME||'';
PREPARE CURSOR_STMT1 FROM CURSOR_STMT;

END;

call X.SP_DYN ('X','2008-08-01 18:17:32.000000')
SQL0104N An unexpected token ".32" was found following "S =
2008-08-01-18.17". Expected tokens may include: "SELECTIVITY".
SQLSTATE=42601

If I include quote before :i.e
SET CURSOR_STMT ='SELECT COUNTRY_RTBL.COUNTRY_CD,COUNTRY_RTBL.COUNTRY_NM,A. DM_LINEAGE_ID
FROM '||SCHEMA_NAME||'.COUNTRY_RT COUNTRY_RT,'||SCHEMA_NAME||'.ETL_LINE A
WHERE A.DM_LINEAGE_CREATE_TS = '''||CAST('2008-08-01 18:17:32.000000' AS CHAR(26) ) ||'''AND A.PROCESS_NM = '||PROCESS_NAME||'';
I get below error
call IDM_DEV.SP__DYN ('SP_DYN','2008-08-01 18:17:32.000000','IDM_DEV',?,?);
SQL0206N "SP_DYN" is not valid in the context where it is used.
SQLSTATE=42703


Statement processed with ERROR.

Thanks in advance

Yes, I see:


... ||'''AND A.PROCESS_NM = '||PROCESS_NAME||'' ...


1) add a blanc before the AND
2) you need some quotes before and after PROCESS_NAME.

try:
|| ''' AND A.PROCESS_NM = ''' || PROCESS_NAME || ''''