Hi,
There are several ways to use transactions. You can do it like this (modified and added code in bold):
CREATE PROCEDURE [dbo].[InsertRowInPersonelInfo]
(@personelcode varchar (20),@firstname nvarchar(50),@lastname nvarchar(50),
@employmenttype nvarchar(50),@employmentdate char(10))
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
insert into PersonelInfo(PersonelCode,FirstName,LastName)
Values(@personelcode,@firstname,@lastname)
DECLARE @lstprsnlID INT
set @lstprsnlID = SCOPE_IDENTITY();
insert into EmploymentInfo(FK_pID,EmploymentType,EmploymentDate)
Values(@lstprsnlID,@employmenttype,@employmentdate)
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF (XACT_STATE()) = -1
ROLLBACK TRANSACTION;
ELSE IF (XACT_STATE()) = 1
COMMIT TRANSACTION;
END CATCH;
END
GO
You can find more information here:
1.
http://technet.microsoft.com/en-us/library/ms175976.aspx[
^]
(B. Using TRY…CATCH in a transaction; C. Using TRY…CATCH with XACT_STATE)
2.
http://technet.microsoft.com/en-us/library/ms174377.aspx[
^]