* 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
-------------------------------------
-------------------------------------