Assign CURRENT_DATE to flowgraph variable via Stored Procedure

[#1]

Hi everyone,

We are working with SDI in the Web-based Development Workbench v 1.120.38.

Via a stored procedure, I am trying to run a flowgraph that will fill in the target table with either a date I choose myself, or, if this is null, today's date by default. I can perfectly do it by running the flowgraph directly. I defined a "DATE" variable with default value: CURRENT_DATE. Then, I fill in the column of my target table with $$DATE$$.

Now, I am trying to pass this default value from a stored procedure, because I would like to run several flowgraphs via this same stored procedure. But I never have the date of today. It's like the CURRENT_DATE of the stored procedure is '2002-01-01' and I don't understand from where this values comes from.

Here is a simpler script of a stored procedure trying to pass the current date into the variable of my flowgraph:

PROCEDURE "ZMODELING"."5_StoredProcedures::SP_START_ZTEST_VARIABLE" ( )

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

DEFAULT SCHEMA "MYSCHEMA" AS

BEGIN DECLARE EX_DATE NVARCHAR(21);

DECLARE EXIT HANDLER FOR SQLEXCEPTION

SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FROM DUMMY;

BEGIN AUTONOMOUS TRANSACTION

EX_DATE:=CURRENT_DATE;

END;

EXEC 'START TASK "ZMODELING"."1_Flowgraphs::ZFL_TEST_VARIABLE"("DATE" => ''' ||EX_DATE||''' )';

END

But, it is always inserting '2002-01-01' in the target table.

I also tried other statements for the assignation like "SELECT CURRENT_DATE INTO EX_DATE FROM dummy;" but nothing is working, it's like the current date for variables in stored procedure is '2002-01-01'.

Even running the stored procedure only with EXEC 'START TASK "ZMODELING"."1_Flowgraphs::ZFL_TEST_VARIABLE"("DATE" => ''' ||CURRENT_DATE||''' )'; will give a date of '2002-01-01'.

Can someone help me? Do you also have '2002-01-01' ?

What could also help me is knowing how to start a flowgraph with the default variable from a stored procedure. I tried to start the flowgraph without input variable or with an NULL value as input variable but this is throwing me an error.

Thank you for reading me!

[former_member601289]

Hi,

Here is the answer. The right syntax to start a flowgraph with the current date is:

EXEC 'START TASK "ZMODELING"."1_Flowgraphs::ZFL_TEST_VARIABLE"("DATE" => '''''||CURRENT_DATE||''''' )';

This means that in the procedure above, we need to define EX_DATE as TO_NVARCHAR('''''||CURRENT_DATE||''''') instead of simply EX_DATE := CURRENT_DATE. We need to add 5 single quotes around the CURRENT_DATE formula.

Regards,

Amandine