Stored procedure for passing date values

Database
Enthusiast

Stored procedure for passing date values

Hi Team, Would you please help me to write a procedure which should pass the parameters of date values. I just wanted use the procedure for passing values rather than hard-coding values in my filter condition,  How can I make this condition into procedure way.

Thanks in advance!

Existing condition:
and a.dlvr_dt between current_date - 365 and current_date

Output should be:

and a.dlvr_dt between current_date – CALL PROCEDURE(example)
and current_date

Mahesh

4 REPLIES
Enthusiast

Re: Stored procedure for passing date values

Hi Team, I have created the procedure and it was returning expected values. But How can i use the procedure in my where condition ( everything in BTEQ script) as I have mentioned previous post.

Would you please help us use this procedure in WHERE condition of update statement which was in there BTEQ script.

CREATE PROCEDURE TWO_WEEK_SPACE.PROC_TO_SEND_DATA (OUT DAYS INTEGER)
BEGIN
SEL TRIM (NO_OF_DAYS) (TITLE '') FROM TWO_WEEK_SPACE.CBS_INFO_FEED_PROC
INTO :DAYS;
END

CREATE TABLE TWO_WEEK_SPACE.CBS_INFO_FEED_PROC
(
SCRIPT_NAME VARCHAR(30),
TABLENAME1 VARCHAR(30),
NO_OF_DAYS INTEGER
);

select * from dbc.tables
Where databasename='DATA_LAB'

RED_SCORE_USER

INSERT INTO TWO_WEEK_SPACE.CBS_INFO_FEED_PROC
(
SCRIPT_NAME,
TABLENAME1,
NO_OF_DAYS
)
VALUES
(
'cbs_infousa_data.btq',
'Info_USA_Extract'
,365
);

CALL TWO_WEEK_SPACE.PROC_TO_SEND_DATA(INP);--It returns 365 value

I wanted to use something like below..

and a.dlvr_dt between
current_date – CALL TWO_WEEK_SPACE.PROC_TO_SEND_DATA(INP);
and current_date

Regards,

Mahesh



Junior Contributor

Re: Stored procedure for passing date values

Hi Mahesh,

you can't use an SP in a SELECT.

Why do you actually need it?

Your example can easily be rewritten without it. 

Enthusiast

Re: Stored procedure for passing date values

Hi Dieter,

Thank you for your confirmation, Actually that passing value(365) will be changed soon as per busniess requirement. I wnat to write a procedure which has to pass the value (Means once requirement changes then I have to insert new record into Procedure table, No need to do any coding changes at BTEQ level).

By the way, we can use the base table in that WHERE clause

AND A.DLVR_DT BETWEEN CURRENT_DATE – (SEL NO_OF_DAYS FROM TWO_WEEK_SPACE.CBS_INFO_FEED_PROC) AND CURRENT_DATE

But instead of Base table(Not standard way), So I wnated to use a procedure.

Thank you,

Mahesh

Junior Contributor

Re: Stored procedure for passing date values

Hi Mahesh,

why is using a SP more standard than using a table?

If you use this the optimizer can do Incremental Planning in TD14.10:

AND A.DLVR_DT BETWEEN CURRENT_DATE
– (SEL MIN(NO_OF_DAYS) FROM TWO_WEEK_SPACE.CBS_INFO_FEED_PROC)
AND CURRENT_DATE;