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 ?

Pradeep Shet · Answer

Hi Gaurav Its simple I am showing delete of records without timestamp. BEGIN TRAN DELETE FROM tbl WHERE CAST(datecolumn 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.

Ramesh Palaniappan · Answer

BEGIN T1 DELETE FROM TABLE WHERE CAST (DateColumnUsed AS DATE ) CAST (GETDATE() AS DATE ); COMMIT T1 END T1 BEGIN CATCH ROLLBACK T1 END CATCH

Nepethya Rana · Answer

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

Midhun T P · Answer

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

Krishna Rajput Singh · Answer

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