CODEWITHSUNDEEP
db2db2-luw
HHH
HHH

Reputation: 6465

How to use input variable in a stored procedure in DB2

I'm creating a stored procedure which takes an input variable and does some comparison in a dynamic SQL queries. However the variable is not picked up, how can I use a variable in here. Here is how my SP looks like

CREATE OR REPLACE PROCEDURE SP1(
IN start_date VARCHAR(20))

DYNAMIC RESULT SETS 1
P1: BEGIN

DECLARE SQLCODE integer;
DECLARE table_exists integer default 0;
DECLARE myQuery VARCHAR(2000);

SET myQuery = "CREATE TABLE XYZ AS(SELECT * FROM TABLE WHERE date > "+ start_date + ") WITH DATA"

execute immediate(myQuery)
END P1

----------REVISED ---------------

My CREATE STATEMENT is actually longer than what I've written here and I need a long VARCHAR variable myquery, but I'm getting an error that 

``is too long.  The maximum length is "128"..``

What should I do?

What changes I need to make to make it work??

Upvotes: 0

Views: 2384

Answers (1)

Paul Vernon
Paul Vernon

Reputation: 3901

You can't use "+" on stings in Db2. Use ||. You were also missing some semi-colons. This code runs for me

CREATE OR REPLACE PROCEDURE SP1(
IN start_date VARCHAR(20))

DYNAMIC RESULT SETS 1
P1: BEGIN

DECLARE SQLCODE integer;
DECLARE table_exists integer default 0;
DECLARE myQuery VARCHAR(200);

SET myQuery = 'CREATE TABLE XYZ AS(SELECT * FROM TABLE WHERE date > '|| start_date ||') WITH DATA';

execute immediate(myQuery);
END P1

however you don't actually need to use dynamic SQL if you are only parameterising the date.. i.e. this also works

CREATE OR REPLACE PROCEDURE SP1(
IN start_date DATE)

DYNAMIC RESULT SETS 1
P1: BEGIN

CREATE TABLE XYZ AS(SELECT * FROM TABLE WHERE date > start_date) WITH DATA;

END P1

Still you said that you "need a long VARCHAR".. well use a CLOB and you have no practical limit

CREATE OR REPLACE PROCEDURE SP1(
IN start_date VARCHAR(20))

DYNAMIC RESULT SETS 1
P1: BEGIN

DECLARE SQLCODE integer;
DECLARE table_exists integer default 0;
DECLARE myQuery CLOB(200000);

SET myQuery = 'CREATE TABLE XYZ AS(SELECT * FROM TABLE WHERE date > '|| start_date ||') WITH DATA';

execute immediate(myQuery);
END P1

Upvotes: 1

Related Questions