Step 1: First Create Store Procedure.

Step 2: Create Try and Catch Block.

Step 3: Write Begin TRAN before all executable statement and Commit TRAN after all transaction is done.

Step 4: Write in catch block for Rollback as Rollback TRAN if transaction is fail.

Show below example for more details.

CREATE PROCEDURE AddUpdateLocation_Sp
(
@p_Locationid BIGINT
,@p_name varchar
,@p_latitude varchar
,@p_longtude varchar
,@p_code varchar
,@p_radius varchar
,@p_Error INT OUTPUT

)
**************************************************************************
Change History
**************************************************************************
Date Author Revision Description
---------------------------------------------------
17/JAN/2017 JKD Created

***************************************************************************/
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @l_ErrNum INT
DECLARE @l_ErrMsg VARCHAR(1000)
DECLARE @l_ErrProc VARCHAR(50)
DECLARE @l_Error INT

BEGIN TRY

-- Insert statements for procedure here
SET @p_Error=0
if(@p_Locationid=0)
BEGIN

BEGIN TRAN
-- Insert

COMMIT TRAN
END
ELSE
BEGIN

BEGIN TRAN
-- update

COMMIT TRAN
END
END TRY
BEGIN CATCH

--IF @@TRANCOUNT > 0
--ROLLBACK TRANSACTION;

--THROW ;
SET @p_Error = 5004--Error while adding or updating to db

ROLLBACK TRAN

SELECT @l_ErrNum = ERROR_NUMBER(),
@l_ErrProc = ISNULL(ERROR_PROCEDURE(), '-');

SELECT @l_ErrMsg = N'Error %d, Procedure %s, ' + 'ErrorMessage: '+ ERROR_MESSAGE();
RAISERROR (@l_ErrMsg, 16,1,@l_ErrNum,@l_ErrProc);
END CATCH
END