October 18, 2006 at 7:47 am
I have a scenario similar to this which one do you suggest or a better way
CREATE PROCEDURE MyProc
@EmpName VARCHAR(50),
@Sal NUMERIC(18,2)
AS
BEGIN
DECLARE @EmpID INT
DECLARE @ERROR INT
SET @ERROR = 0
BEGIN TRANSACTION MyTran
INSERT INTO dbo.Employee
(EmployeeName)
VALUES
(@EmpName)
SELECT @EmpID = @@IDENTITY,@ERROR=@@ERROR
IF @ERROR <>0
ROLLBACK TRANSACTION MyTran
INSERT INTO dbo.Salary
(EmpID,
Sal)
VALUES
(@EmpID,
@Sal)
SET @ERROR = @@ERROR
IF @ERROR <>0
ROLLBACK TRANSACTION MyTran
IF @ERROR =0
COMMIT TRANSACTION MyTran
SELECT @EmpID AS EmployeeID
END
---------------------------------------------------------------------------------------
OR
CREATE PROCEDURE MyProc
@EmpName VARCHAR(50),
@Sal NUMERIC(18,2)
AS
BEGIN
DECLARE @EmpID INT
DECLARE @ERROR INT
SET @ERROR = 0
BEGIN TRANSACTION MyTran
BEGIN TRAN MyEmpInsTran
INSERT INTO dbo.Employee
(EmployeeName)
VALUES
(@EmpName)
SELECT @EmpID = @@IDENTITY,@ERROR=@@ERROR
IF @ERROR <>0
ROLLBACK TRANSACTION MyEmpInsTran
IF @Error = 0
BEGIN TRAN MySalInsTran
INSERT INTO dbo.Salary
(EmpID,
Sal)
VALUES
(@EmpID,
@Sal)
SET @ERROR = @@ERROR
IF @ERROR <>0
ROLLBACK TRANSACTION MySalInsTran
IF @ERROR =0
COMMIT TRANSACTION MyEmpInsTran
COMMIT TRANSACTION MySalInsTran
COMMIT TRANSACTION MyTran
SELECT @EmpID AS EmployeeID
END
October 18, 2006 at 8:03 am
As you are not raising any special errors, why not keep it simple:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.MyProc
( @EmployeeName VARCHAR(50)
-- MONEY may be better for Sal, or at least DECIMAL(18,4)
,@Sal NUMERIC(18,2)
,@EmpID INT OUTPUT )
AS
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT dbo.Employee(EmployeeName)
SELECT @EmployeeName
SET @EmpID = SCOPE_IDENTITY()
INSERT dbo.Salary(EmpID, Sal)
SELECT @EmpID, @Sal
COMMIT
GO
October 18, 2006 at 8:07 am
Infact on error I need to Return EmpID AS -1
October 18, 2006 at 8:18 am
If you REALLY have to:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.MyProc
( @EmployeeName VARCHAR(50)
-- MONEY may be better for Sal, or at least DECIMAL(18,4)
,@Sal NUMERIC(18,2)
,@EmpID INT OUTPUT )
AS
SET NOCOUNT ON
--SET XACT_ABORT ON
DECLARE @Err INT
BEGIN TRANSACTION
INSERT dbo.Employee(EmployeeName)
SELECT @EmployeeName
SELECT @Err = @@ERROR, @EmpID = SCOPE_IDENTITY()
IF @Err <> 0 GOTO Ditch
INSERT dbo.Salary(EmpID, Sal)
SELECT @EmpID, @Sal
SET @Err = @@ERROR
IF @Err <> 0 GOTO Ditch
COMMIT
GOTO TheEnd
Ditch:
SET @EmpID = -1
ROLLBACK
TheEnd:
RETURN @Err
GO
October 18, 2006 at 10:43 am
Thank you
-Ravi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply