Insert system time in database

Dear Friends,

I want to insert in my database, in a table field the system time value on that moment.

For example: I want to create the follow stored procedure:

CREATE PROCEDURE TEST

@.ID INT

AS

UPDATE TABLE1 SET MyFieldTime=@.MySystemTime WHERE MyFieldID=@.ID

I want to save in my database th system time...

Thanks!!

UPDATE TABLE1 SET MyFieldTime=getdate() WHERE MyFieldID=@.ID|||

it always good practice to use the UTC time instead System's local time. In future if you transfer the data from one server (time zone) to another you need not to applay any changes..

Another benifit on UI you can convert to any Local time from UTC with out any overhead...

use the following query..

Update Table1 Set MyFiedlTime = GetUTCDate() Where MyFieldId = @.ID

|||

You should do this using a DEFAULT on the column and then use DEFAULT keyword in the SET clause of whatever UPDATE statement that modifies the data. Using a separate SP is not really a good idea since you will decouple the actual update and the time when it was done. It is also costly to perform multiple updates on the same row when you can do it once.

You can make below changes:

alter table TABLE1 add default( CURRENT_TIMESTAMP ) for MyFieldTime

After that when you do the actual UPDATE then do:

UPDATE TABLE1

SET col1 = ...

, col2 = ...

, MyFieldTime = DEFAULT

WHERE ...

Insert Statement

Hi Friends,
In my SP i have 3 insert statements that inserts record into 3 different
tables. If any of the inserts fail, I want to roll back any other inserts
that is executed. how to do this. please give me an example.
example:
insert into abc values('ert','ert')
insert into xyz values('rtert','rtyrty')
if the insert operation fails for the table xyz then the insert for abc
should not be commited.
thnks
vanithaput your sql statement in a transaction
begin transaction
insert 1....
insert 2....
insert 3.......
commit transaction
<hr>
MCP #2324787
"Vanitha" wrote:

> Hi Friends,
> In my SP i have 3 insert statements that inserts record into 3 different
> tables. If any of the inserts fail, I want to roll back any other inserts
> that is executed. how to do this. please give me an example.
> example:
> insert into abc values('ert','ert')
> insert into xyz values('rtert','rtyrty')
> if the insert operation fails for the table xyz then the insert for abc
> should not be commited.
> thnks
> vanitha|||CREATE PROCC dbo.ProcedureName
(paramlist)
AS
BEGIN
BEGIN TRAN [tranname]
INSERT INTO First table ...
IF @.@.ERROR > 0
BEGIN
ROLLBACK TRAN [tranname]
RETURN [Errorcode]
END
INSERT INTO second table ...
IF @.@.ERROR > 0
BEGIN
ROLLBACK TRAN [tranname]
RETURN [Errorcode]
END
INSERT INTO third table ...
IF @.@.ERROR > 0
BEGIN
ROLLBACK TRAN [tranname]
RETURN [Errorcode]
END
COMMIT TRAN [tranname]
END
GO
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:08B477A8-5335-45EB-BAC2-B76B2B2A5563@.microsoft.com...
> Hi Friends,
> In my SP i have 3 insert statements that inserts record into 3 different
> tables. If any of the inserts fail, I want to roll back any other inserts
> that is executed. how to do this. please give me an example.
> example:
> insert into abc values('ert','ert')
> insert into xyz values('rtert','rtyrty')
> if the insert operation fails for the table xyz then the insert for abc
> should not be commited.
> thnks
> vanitha|||This involves error handling as well as transaction handling. It is a large
topic, so some
background reading will sort this out for you. The best reference to this to
pic, IMO, is the error
handling articles at:
http://www.sommarskog.se/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:08B477A8-5335-45EB-BAC2-B76B2B2A5563@.microsoft.com...
> Hi Friends,
> In my SP i have 3 insert statements that inserts record into 3 different
> tables. If any of the inserts fail, I want to roll back any other inserts
> that is executed. how to do this. please give me an example.
> example:
> insert into abc values('ert','ert')
> insert into xyz values('rtert','rtyrty')
> if the insert operation fails for the table xyz then the insert for abc
> should not be commited.
> thnks
> vanitha|||Just adding BEGIN TRAN and COMMIT TRAN will not cut it. The first might be O
K, the second fail and
the third OK. So the first and the third are performed but not the second. Y
ou need error handling
as well (or SET XACT_ABORT ON, but almost no-one in the SQL Server community
uses this setting).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:80DA1215-F68E-4FA3-BE39-3B08BD39473A@.microsoft.com...
> put your sql statement in a transaction
>
> begin transaction
> insert 1....
> insert 2....
> insert 3.......
> commit transaction
>
> --
>
> <hr>
> MCP #2324787
>
> "Vanitha" wrote:
>|||Thanks
"Tibor Karaszi" wrote:

> This involves error handling as well as transaction handling. It is a larg
e topic, so some
> background reading will sort this out for you. The best reference to this
topic, IMO, is the error
> handling articles at:
> http://www.sommarskog.se/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
> news:08B477A8-5335-45EB-BAC2-B76B2B2A5563@.microsoft.com...
>