Cari Blog Ini

08 Januari 2014

How To Create an Stored Procedure with COMMIT or ROLLBACK Transaction

Apply To :
* Microsoft SQL Server 2008 R2


Want to :

* Create an procedure with syntax COMMIT TRANSACTION and ROLLBACK TRANSACTION






1. Example Table "Temp_Currency_1"

----------------------------------------------------------
----------------------------------------------------------
-----CODE START

CREATE TABLE [dbo].[Temp_Currency_1]
(
 [Currency] [varchar](5) NOT NULL,
 [Description] [varchar](50) NULL
) ON [PRIMARY]

-----CODE END
----------------------------------------------------------
----------------------------------------------------------




2. Create a sampling data,
----------------------------------------------------------
----------------------------------------------------------
-----CODE START

insert into Temp_Currency_1 values ('AUD','Australian Dollar')
insert into Temp_Currency_1 values ('EUR','Euro Eropa')
insert into Temp_Currency_1 values ('USD','US Dollar')

-----CODE END
----------------------------------------------------------
----------------------------------------------------------





3. To see the data,
----------------------------------------------------------
----------------------------------------------------------
-----CODE START

select * from Temp_Currency_1

-----CODE END
----------------------------------------------------------
----------------------------------------------------------





4. Type the code below,

----------------------------------------------------------
----------------------------------------------------------
-----CODE START

create  procedure A_Insert_Currency
@nCurrency varchar(20),
@nDescription varchar(200)
AS

Declare @EffDate datetime,
@lclExpDate datetime


BEGIN 

Declare @Result varchar(1),@ResultDesc varchar(100)
Set @Result = 'Y'
Set @ResultDesc = 'Insertion Of New Currency Successfully...'


Begin transaction TRANS1

If Exists(select 1 from Temp_Currency_1 where currency = @nCurrency)
Begin
print 'The record already Exists'
Set @Result = 'N'
set @ResultDesc = 'The record already Exists'
End 

Else
Begin
print 'Record does not exists'
insert into Temp_Currency_1 
(Currency,Description) values
(@nCurrency,@nDescription)
If @@error <> 0 
Begin
Set @Result = 'N'
set @ResultDesc = 'Insertion Failed... '
goto ProcExit
End
End
End 


ProcExit:
If @Result = 'Y' 
Begin
commit transaction TRANS1
print 'Committed Transaction'
End
Else
Begin
rollback transaction TRANS1
Print 'Roll Backed Transaction '
End


select @Result as Result,  @ResultDesc as ResultDesc


-----CODE END
----------------------------------------------------------
----------------------------------------------------------






5. Try to run the procedure,

A. Example, if there is an exists data

-----------------------------------
------------------------------------
-----CODE START

exec A_Insert_Currency 'USD', 'US Dollar'

-----CODE END
-------------------------------------
-------------------------------------




B. Example, if there is an new data

------------------------------------
------------------------------------
-----CODE START

exec A_Insert_Currency 'IDR', 'Rupiah'
select * from Temp_Currency_1

-----CODE END
-------------------------------------
-------------------------------------







6. Optional, to remove the procedure

------------------------------------
------------------------------------
-----CODE START

drop table Temp_Currency_1
drop procedure A_Insert_Currency

-----CODE END
-------------------------------------
-------------------------------------