Syntax to create store proc with transaction commit and roll

Question

How do I create store procedure with commit and rollback to delete records that are older than current date ?

Answer 1

Hi Gaurav,

Its simple, I am showing delete of records without timestamp.

BEGIN TRAN

DELETE FROM tbl WHERE CAST(datecolumn AS DATE) < CAST(GETDATE() AS DATE);

COMMIT TRAN;

END TRY

BEGIN CATCH

ROLLBACK TRAN;

END CATCH

Hope this is what you are expecting. If this answers your query, dont forget to mark it as answered.

Answer 2

BEGINT1
DELETEFROMTABLEWHERECAST(DateColumnUsedASDATE)<CAST(GETDATE()ASDATE);
COMMITT1
ENDT1
BEGINCATCH
ROLLBACKT1
ENDCATCH

Answer 3

Thank you all of you. I wish i could accept all of the answers but system allows only one. But you all have helped me. Thank you

Answer 4

Hi,

Please check below link -

http://www.infoa2z.com/sqlserver/use-of-begin-commit-transaction-rollback-in-a-stored-procedure-sql-server

Answer 5

Thank yiu for writing below links helpful for you....

https://msdn.microsoft.com/en-IN/library/ms188929.aspx

http://www.4guysfromrolla.com/webtech/080305-1.shtml