On Mon, 30 Sep 2002 15:29:07 -0400, Mike Mascari <[EMAIL PROTECTED]> wrote: > I'm wondering how the others handle multiple >references in CURRENT_TIMESTAMP in a single stored >procedure/function invocation.
MSSQL 7 seems to evaluate CURRENT_TIMESTAMP for each statement, Interbase 6 once per procedure call. Here are my test procedures: MSSQL 7 create table tst (i integer, d datetime not null) go create procedure tstInsert as begin delete from tst insert into tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b, tst c, tst d, tst e insert into tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b, tst c, tst d, tst e insert into tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b, tst c, tst d, tst e insert into tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b, tst c, tst d, tst e insert into tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b, tst c, tst d, tst e insert into tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b, tst c, tst d, tst e insert into tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b, tst c, tst d, tst e insert into tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b, tst c, tst d, tst e insert into tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b, tst c, tst d, tst e insert into tst(i, d) select count(*),CURRENT_TIMESTAMP from tst a, tst b, tst c, tst d, tst e end go begin transaction exec tstInsert commit transaction select * from tst i d ----------- --------------------------- 0 2002-09-30 22:26:06.540 1 2002-09-30 22:26:06.540 32 2002-09-30 22:26:06.540 243 2002-09-30 22:26:06.540 1024 2002-09-30 22:26:06.550 3125 2002-09-30 22:26:06.550 7776 2002-09-30 22:26:06.550 16807 2002-09-30 22:26:06.560 32768 2002-09-30 22:26:06.570 59049 2002-09-30 22:26:06.590 (10 row(s) affected) Interbase 6 SQL> create table tst(i integer, d timestamp); SQL> commit; SQL> set term !!; SQL> create procedure tstInsert as begin CON> delete from tst; CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP CON> from tst a, tst b, tst c, tst d, tst e; CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP CON> from tst a, tst b, tst c, tst d, tst e; CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP CON> from tst a, tst b, tst c, tst d, tst e; CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP CON> from tst a, tst b, tst c, tst d, tst e; CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP CON> from tst a, tst b, tst c, tst d, tst e; CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP CON> from tst a, tst b, tst c, tst d, tst e; CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP CON> from tst a, tst b, tst c, tst d, tst e; CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP CON> from tst a, tst b, tst c, tst d, tst e; CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP CON> from tst a, tst b, tst c, tst d, tst e; CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP CON> from tst a, tst b, tst c, tst d, tst e; CON> end; CON> !! SQL> set term ; !! SQL> commit; SQL> execute procedure tstInsert; -- takes approx. 5 seconds. SQL> select * from tst; I D ============ ========================= 0 1858-11-17 00:00:00.0000 1 2002-09-30 22:37:54.0000 32 2002-09-30 22:37:54.0000 243 2002-09-30 22:37:54.0000 1024 2002-09-30 22:37:54.0000 3125 2002-09-30 22:37:54.0000 7776 2002-09-30 22:37:54.0000 16807 2002-09-30 22:37:54.0000 32768 2002-09-30 22:37:54.0000 59049 2002-09-30 22:37:54.0000 SQL> commit; BTW, it's interesting (but OT) how they handle select count(*), current_timestamp, 1 from tst where 0=1; differently. MSSQL: 0 2002-09-30 22:53:55.920 1 Interbase: 0 1858-11-17 00:00:00.0000 0 <--- bug here? Postgres: 0 2002-09-30 21:10:35.660781+02 1 Servus Manfred ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html