Explain stored procedure with transaction and try-catch BLOCK in SQL SERVER.

For achieving this task first you made a table-

Employee Table Query:

CREATE TABLE Employee(
[ID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeCode] [nvarchar](100) NOT NULL,
[EmployeeName] [nvarchar](100) NOT NULL,
[Mobile] [nvarchar](100) NOT NULL,
[Email] [nvarchar](100) NULL,
[Password] [nvarchar](100) NULL,
                            ) 

Procedure for Employee table-

CREATE PROCEDURE Employee

(

@ID INT = NULL,

@EmployeeCode NVARCHAR(100)=NULL,

@EmployeeName  NVARCHAR(100) = NULL,

@Mobile  NVARCHAR(100) = NULL,

@Email  NVARCHAR(100) = NULL,

@Password  NVARCHAR(100) = NULL,

@flag  VARCHAR(100) =NULL out

)

AS

BEGIN

SET NOCOUNT ON;

BEGIN TRY

BEGIN TRANSACTION

                      BEGIN

  INSERT INTO UserMaster([EmployeeCode],[EmployeeName],[Mobile],[Email],[Password])

   VALUES(@EmployeeCode,@EmployeeName,@Mobile,@Email,@Password) ;

   SET @flag='success';

                      END

COMMIT TRANSACTION;

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

BEGIN

ROLLBACK TRANSACTION;

SET @flag ='Something went wrong! Please check your inputs and try again.';

END

END CATCH

SET NOCOUNT OFF;

END


Need Help? Contact Us.

Log in

*
*

Forgot password?

*

New User

*
*
*
*