start and end transcation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

start and end transcation

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-03-19 : 05:25:02

I have below statements

can anybody let me know where should I start and end transcation
currently for below code it's showing an error as

Incorrect syntax near the keyword 'TRAN'.
---------------------------------------------------------------------
Declare @LastRunDate as varchar(100)
set @LastRunDate='2005-12-31'
BEGIN TRY
BEGIN TRAN
delete from [PROD_SUM] where exists
(
SELECT * FROM [PROD_SUM] where [KC_PROD_SUM_MES_PRODU_DATE_DT] <= @LastRunDate
);
END TRAN
END TRY

BEGIN CATCH
IF @@ERROR <> 0
ROLLBACK TRAN
-- Raise an error with the details of the exception
DECLARE @ErrMsg1 nvarchar(4000),
@ErrSeverity1 int,
@ErrorState1 INT
SELECT @ErrMsg1 = ERROR_MESSAGE(),
@ErrSeverity1 = ERROR_SEVERITY(),
@ErrorState1 = ERROR_STATE()

RAISERROR(@ErrMsg1, @ErrSeverity1, @ErrorState1)
END CATCH
---------------------------------------------------------------------------
BEGIN TRY
BEGIN TRAN
Delete from [PROD_WASTE] where exists
(
SELECT * FROM [PROD_WASTE] where [KC_PROD_WASTE_MES_PRODU_DATE_D] <= @LastRunDate--getdate()
)
END TRAN
END TRY

qutesanju
Posting Yak Master

193 Posts

Posted - 2010-03-19 : 05:27:24

Posting appropriate code again
Declare @LastRunDate as varchar(100)
set @LastRunDate='2005-12-31'
BEGIN TRY
BEGIN TRAN
delete from [PROD_SUM] where exists
(
SELECT * FROM [PROD_SUM] where [KC_PROD_SUM_MES_PRODU_DATE_DT] <= @LastRunDate
);
END TRAN
END TRY

BEGIN CATCH
IF @@ERROR <> 0
ROLLBACK TRAN
-- Raise an error with the details of the exception
DECLARE @ErrMsg1 nvarchar(4000),
@ErrSeverity1 int,
@ErrorState1 INT
SELECT @ErrMsg1 = ERROR_MESSAGE(),
@ErrSeverity1 = ERROR_SEVERITY(),
@ErrorState1 = ERROR_STATE()

RAISERROR(@ErrMsg1, @ErrSeverity1, @ErrorState1)
END CATCH
---------------------------------------------------------------------------
BEGIN TRY
BEGIN TRAN
Delete from [PROD_WASTE] where exists
(
SELECT * FROM [PROD_WASTE] where [KC_PROD_WASTE_MES_PRODU_DATE_D] <= @LastRunDate--getdate()
)
END TRAN
END TRY
BEGIN CATCH
IF @@ERROR <> 0
ROLLBACK TRAN
-- Raise an error with the details of the exception
DECLARE @ErrMsg2 nvarchar(4000),
@ErrSeverity2 int,
@ErrorState2 INT
SELECT @ErrMsg2 = ERROR_MESSAGE(),
@ErrSeverity2 = ERROR_SEVERITY(),
@ErrorState2 = ERROR_STATE()

RAISERROR(@ErrMsg2, @ErrSeverity2, @ErrorState2)
END CATCH

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-19 : 08:59:44

The proper code for starting implicit transactions is:

BEGIN TRAN
--> Do your stuff...
IF @SomethingWrongHappenedSomewhere = 1
  ROLLBACK
ELSE
  COMMIT

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources