Rollback deleted records if execution failed in SQL Server

Hi Waghmare,

I am working on it, I will get back to you soon.

Hi waghmare,

Use SQL TRANSACTION.

Please refer below query.

SQL

CREATE PROCEDURE [Remove_Data]
    @Id INT
AS 
BEGIN
	BEGIN TRANSACTION;
	BEGIN TRY
		DECLARE @State_ID INT;
		SELECT @State_ID = Id FROM [State] WHERE Country_Id=@Id
		DELETE  FROM [City] WHERE State_ID=@State_ID
		DELETE  FROM [State] WHERE Country_ID=@ID
		DELETE  FROM [Country] WHERE Id=@Id
		COMMIT TRANSACTION;
	END TRY
	BEGIN CATCH
		ROLLBACK TRANSACTION;
	END CATCH
END