Search code examples
teradatadeclare

Teradata declaring dates at start of the script

This may seem like a very basic question.

I have a very large SQL statement with lots of sub queries that contain date limits in multiple where clauses

We run this query on an ad-hoc basis where i have to change the date range in the query in about 20 places. The date range is the same in all the places. So for example 1-Jan-2016 to 7-Jan-2016 as an example

In Teradata is it possible to declare the date range at the start of the query for example like a variable and then reference this variable in the code so i only need to change it once?

I have seen the answer for declaration of variable in teradata but would like to see a simple example demonstrating the concept for a date range in a stored procedure

Thank you for your time

Solution

  • Instead of making it a variable, it should probably be a parameter. Your stored proc would be something like this:

    REPLACE PROCEDURE MyStoredProcedure
(
IN StartDate DATE
,IN EndDate DATE
) 
BEGIN
DECLARE SomeOtherDate DATE; --if you need an actual date variable
--Your logic goes here

END;

    Then you would set the parameters when you call the stored proc

    CALL MyStoredProcedure('2015-01-01','2015-12-31');