Our Training Programs

All contents are copyright of their authors.

Syntax to create store proc with transaction commit and roll

Syntax to create store proc with transaction commit and roll

Nepethya Rana

10yAsked Sep 23, 2016, 12:52 PM854

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

Know the answer? Post it — somebody with the same question will find it here.

It is the same account you read, post and publish with — and you will come straight back to this page.

Pradeep Shet10y ago
Accepted answer
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.
Nepethya Rana10y ago
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
Ramesh Palaniappan10y ago
1. BEGINT1
2. DELETEFROMTABLEWHERECAST(DateColumnUsedASDATE)<CAST(GETDATE()ASDATE);
3. COMMITT1
4. ENDT1
5. BEGINCATCH
6. ROLLBACKT1
7. ENDCATCH

Hi,

Please check below link -

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

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