Total Page Preview:   000000015637

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

Transaction without try catch
 
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



Comments

Meredith
03-Jul-2019
Hi I tried the version using subquery but got the following error: Parse error at line: 3, column: 56: Incorrect syntax near '('.

                           
                           

                           

                           

Facebook User: