Total Page Preview: 000000017498
Can we use Transaction without try-catch block in SQL Server ?
Yes we can use transaction without try-catch block using @@ERROR By default @@ERROR will be (Zero) 0 when any error occure during transaction it will not (Zero) 0.
Transaction without Example :
DECLARE @ERRORNO INT
--Begin transaction
BEGIN TRANSACTION
-- insert record in user table
INSERT INTO [dbo].[tblUser](Id, UserName, Salary) VALUES(2, 'BRIJESH', 2510)
--insert record in employee table
INSERT INTO [dbo].[tblEmployee](EmpId, EmpName, Age) VALUES(533333333335555, 'ROHIT', 35)
-- Get error number when error will occure if not error occure then error would 0
SET @ERRORNO = @@ERROR
--if Erro no will 0 then transaction commit otherwise transaction will rollback
IF(@ERRORNO <> 0)
BEGIN
PRINT('TRANSACTION HAS BEEN ROLLBACK')
PRINT( ' ERROR CODE IS : '+ CONVERT(VARCHAR, @ERRORNO )
ROLLBACK TRANSACTION
END
ELSE
BEGIN
PRINT('TRANSACTION HAS BEEN COMMITED')
COMMIT TRANSACTION
END
Transaction without try-catch Example Screen Shot
Thanks !
Thank You
About Author
Brijesh Kumar
Database Developer
I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL
Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall
and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com