MySql, .NET, Stored Procedures sharing the current date and time with the calling client

Question

I'm writing a stored procedure to update a table:

UPDATE st SET somedate = NOW();

The client of the SP must know the exact date and time generated by the NOW function.

There are two options:

1) the client passes an input parameter (called _now) to the SP giving it the current date and time

UPDATE st SET somedate = _now;

2) the SP returns back the NOW's output to the client into an out parameter

UPDATE st SET somedate = NOW();
SELECT somedate FROM st INTO _now;

What do you think is the best option? Are other options possible?

Answer 1

• varnow = now()
• UPDATE st set somedate = varnow
• return varnow

Answer 2

I suspect that both approaches are wrong.

client of the SP must know the exact date and time

Why? I suspect you really men that the client must be able to identify the records affected by a transaction - but using a timestamp to do that will not be accurate. And its not just a transaction spanning more than 1 second that is the problem. Potentially two such operations may occur in the same second.

If you've got a set of records which you need to identify as belonging to some group then that must be expressed in the schema - the timestamp of the most transaction is obviously not reliable even assuming that you never have further updates on the table.

Add another column or another table and generate a surrogate key to describe the transaction.

C.

Answer 3

i would do something like this:

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varchar(32) unique not null,
created_date datetime not null
)
engine=innodb;


delimiter ;

drop procedure if exists insert_user;

delimiter #

create procedure insert_user
(
in uname varchar(32)
)
proc_main:begin

declare id int unsigned default null;
declare created datetime default null;

set created = now();

insert into users (username, created_date) values (uname, created);

set id = last_insert_id();

-- use id elsewhere maybe...

select id as user_id, created as created_date;

end proc_main #


delimiter ;

call insert_user('f00');
call insert_user('bar');

select * from users;