SQL Server : stored procedure transaction

Question

I have some stored procedures to create the products and some other stuff on my site. Now I am having to run some of them in a transaction. Is it possible or do I have to make a stored procedure only for the transaction?

Can I say something like

BEGIN TRAN
"1. stored procedure"
"2. stored procedure"
COMMIT

Answer 1

You may have to add some error handling:

BEGIN TRAN

BEGIN TRY

   EXEC P1

   EXEC P2

   COMMIT TRAN

END TRY
BEGIN CATCH

  ROLLBACK TRAN

END CATCH

Update it with the C# code (I myself find it much easier to have the trans code out of the sprocs and in the data layer - does compose stored procedures easier at a later stage):

Interested in SQL ? Check out this SQL Certification course by Intellipaat.