How To Create Store proecedure With Sql Transection

How To Create Store proecedure With Sql Transection 


GO
CREATE PROCEDURE dbo.sp_mysp
(
@RoleID int,
@AcessRights varchar(50),
@Name varchar(100),
@IsActive bit,
@IsDeleted bit,
@CreatedBy int,
@ModifiedBy int
)
AS
BEGIN
    BEGIN TRANSACTION;
    SAVE TRANSACTION MySavePoint;
    BEGIN TRY
 insert into UserLogin
 (
 [RoleID] , 
 [AccessRights] ,
 [Name],
 [IsActive] , 
 [IsDeleted] , 
 [CreatedBy] , 
 [CreatedDate], 
 [ModifiedBy] , 
 [ModifiedDate]
 ) 
 values(
 @RoleID ,
 @AcessRights ,
 @Name,
 @IsActive ,
 @IsDeleted ,
 @CreatedBy ,
 getdate() ,
 @ModifiedBy ,
 getdate()
 );
 //Write Table second Insert Query
 END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION MySavePoint; -- rollback to MySavePoint
        END
    END CATCH
    COMMIT TRANSACTION 
END;
GO



No comments:

Post a Comment

Thank You For Your Great Contribution

Featured Post

Sql dynamic query With Join And search using like query with multiple column

Sql dynamic query With Join And search using like query with multiple column declare @keyword varchar(100) DECLARE @sqlCommand va...

Popular Posts