On 29/10/13 12:05, Tim Ward wrote:

> I've got a stored procedure which does some sums on dates, which are
> passed in as parameters.

...

> **BUT** the stored procedure I want to test does different things
> depending on what the date is today, which it determines using the 'NOW'
> pre-defined date literal.
>
> So how do I test this procedure? How do I get 'NOW', used within the
> procedure, to produce different timestamps as needed for the various
> test cases? (Without, of course, typing different things into the
> procedure itself, thus introducing the risk of getting it wrong and
> introducing bugs when I later remove the test code.)

Short answer: You can't (and shouldn't try by fiddling with the system
clock)

Better answer:
Change the function to use an extra variable, normally set to 'NOW' but
also able to be set to whatever for testing.

You can either have one function with the variable set for testing and
left as 'NOW' for production:

SET TERM ^ ;

CREATE PROCEDURE MYFUNC1
( NAME VARCHAR(100) )
RETURNS
( RES VARCHAR(100) )
AS
DECLARE VARIABLE MYNOW TIMESTAMP;
BEGIN
/* Set to a date for testing, 'NOW' for production */
MYNOW = 'NOW';

RES = NAME || ' ' || CAST(EXTRACT(MONTH FROM MYNOW) AS VARCHAR(2));
END^

SET TERM ; ^

Pros: One function, simple
Cons: Awkward to change for testing, may forget for production

Or create two functions, one that takes an extra value to use as "now",
the other that calls the first, passing 'NOW':

SET TERM ^ ;

CREATE PROCEDURE MYFUNC2_RAW
( NAME VARCHAR(100), MYNOW TIMESTAMP )
RETURNS
( RES VARCHAR(100) )
AS
BEGIN
RES = NAME || ' ' || CAST(EXTRACT(MONTH FROM MYNOW) AS VARCHAR(2));
END^

CREATE PROCEDURE MYFUNC2
( NAME VARCHAR(100) )
RETURNS
( RES VARCHAR(100) )
AS
BEGIN
EXECUTE PROCEDURE MYFUNC2_RAW(NAME, 'NOW') RETURNING_VALUES RES;
END^

SET TERM ; ^

Pros: Easy to test via the raw function, won't forget for production,
might be useful if you need to explicitly do something with a specific date.
Cons: Have an extra function lying around (I'm not sure, but I think you
can restrict user access to the raw function whilst still allowing the
plain function to run)

Scott