problem using tran rollback - 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

SQL Server 2005 Forums

Transact-SQL (2005)

problem using tran rollback

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-06-05 : 09:24:08

We tried to run this and got some errors because of privileges. We need to run it again, but would like to use rollbacks instead of any errors. I have added the statements, but I am getting errors when I try to run.

Msg 178, Level 15, State 1, Line 21
A RETURN statement with a return value cannot be used in this context.
Msg 178, Level 15, State 1, Line 95
A RETURN statement with a return value cannot be used in this context.
Msg 178, Level 15, State 1, Line 128
A RETURN statement with a return value cannot be used in this context.
Msg 178, Level 15, State 1, Line 167
A RETURN statement with a return value cannot be used in this context.
Msg 178, Level 15, State 1, Line 198
A RETURN statement with a return value cannot be used in this context.
Msg 178, Level 15, State 1, Line 238
A RETURN statement with a return value cannot be used in this context.
Msg 178, Level 15, State 1, Line 252
A RETURN statement with a return value cannot be used in this context.

Here is the statement:

BEGIN TRAN

DECLARE @current_override char(1)

SELECT @current_override = override_flag
FROM apptable
WHERE company = 2
AND subcompany = 2
AND application_name = 'Policy'

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return 10
END

UPDATE apptable
SET override_flag = 'y'
WHERE company = 2
AND subcompany = 2
AND application_name = 'Policy'

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return 10
END

DECLARE @policy_transaction_temp table (
[Policy_Base] varchar(12)
,[Policy_Suffix] smallint
,[Sequence] int
,[Transaction_Type] char(2)
,[Effective_Date] datetime
,[Installment_Fee] smallmoney
,[Entered_Date] datetime
,[Endorsement] smallint
,[Company] char(1)
,[Subcompany] char(1)
)

INSERT @policy_transaction_temp (
[Policy_Base]
,[Policy_Suffix]
,[Sequence]
,[Transaction_Type]
,[Effective_Date]
,[Installment_Fee]
,[Entered_Date]
,[Endorsement]
,[Company]
,[Subcompany]
)
SELECT policy.policy_base --[Policy_Base]
,policy.policy_suffix --[Policy_Suffix]
,(
SELECT MAX(policy_transaction.sequence)
FROM policy_transaction
WHERE policy_transaction.policy_base = policy.policy_base
AND policy_transaction.policy_suffix = policy.policy_suffix
) + 1 AS sequence --[Sequence]
,'AD' --[Transaction_Type]
,getdate() --[Effective_Date]
,-10.00 --[Installment_Fee]
,getdate() --[Entered_Date]
,policy.endorsement --[Endorsement]
,policy.company --[Company]
,policy.subcompany --[Subcompany]
FROM policy_transaction
INNER JOIN policy
ON policy_transaction.policy_base = policy.policy_base
AND policy_transaction.policy_suffix = policy.policy_suffix
INNER JOIN policy_transaction_fee
ON policy_transaction.policy_base = policy_transaction_fee.policy_base
AND policy_transaction.policy_suffix = policy_transaction_fee.policy_suffix
WHERE (policy_transaction.entered_date >= '2008-08-28')
AND (policy.status <> 'ca')
AND (policy.eft_flag = 'y')
AND (policy.current_flag = 'y')
AND (policy.installment_fees >= 10)
AND (policy_transaction_fee.fee_code = 'ins')
AND cast(policy.policy_base as varchar(100)) + '_' + cast(policy.policy_suffix as varchar(1)) IN (
SELECT cast(policy_transaction_fee.policy_base as varchar(100)) + '_' + cast(policy_transaction_fee.policy_suffix as varchar(1))
FROM policy_transaction_fee
WHERE (fee_code = 'ins')
GROUP BY cast(policy_transaction_fee.policy_base as varchar(100)) + '_' + cast(policy_transaction_fee.policy_suffix as varchar(1))
HAVING (SUM(fee_amount) > 0)
)

GROUP BY policy.policy_base,
policy.policy_suffix,
policy.endorsement,
policy.company,
policy.subcompany

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return 10
END

DECLARE @policy_base varchar(12)

DECLARE c1 CURSOR READ_ONLY
FOR
SELECT policy_base
FROM @policy_transaction_temp

OPEN c1

FETCH NEXT FROM c1
INTO @policy_base

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @insured_balance smallmoney
DECLARE @installment_balance smallmoney

SELECT @insured_balance = Policy.Insured_Balance
,@installment_balance = Policy_Information.Installment_Balance
FROM Policy_Information
INNER JOIN Policy
ON Policy_Information.Policy_Base = Policy.Policy_Base
AND Policy_Information.Policy_Suffix = Policy.Policy_Suffix
WHERE Policy.current_flag = 'Y'
AND Policy_Information.Policy_Base = @policy_base

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return 10
END

INSERT policy_transaction (
[Policy_Base]
,[Policy_Suffix]
,[Insured_Balance]
,[Installment_Balance]
,[Sequence]
,[Transaction_Type]
,[Effective_Date]
,[Entered_Date]
,[Endorsement]
,[Company]
,[Subcompany]
)
SELECT [Policy_Base]
,[Policy_Suffix]
,CASE
WHEN ( @insured_balance > 0 OR @installment_balance = 0 ) THEN -10
ELSE 0
END
,CASE
WHEN ( @insured_balance = 0 AND @installment_balance > 0 ) THEN -10
ELSE 0
END
,[Sequence]
,[Transaction_Type]
,[Effective_Date]
,[Entered_Date]
,[Endorsement]
,[Company]
,[Subcompany]
FROM @policy_transaction_temp
WHERE policy_base = @policy_base

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return 10
END

DECLARE @next_sequence INT

SELECT @next_sequence = (MAX(policy_transaction_fee.sequence)+1)
FROM policy_transaction_fee
WHERE policy_base = @policy_base

INSERT policy_transaction_fee (
[Policy_Base]
,[Policy_Suffix]
,[Sequence]
,[fee_code]
,[fee_amount]
,[entered_by]
,[entered_date]
)
SELECT [Policy_Base]
,[Policy_Suffix]
,@next_sequence
,'INS '
,[Installment_Fee]
,8000
,[Entered_Date]
FROM @policy_transaction_temp
WHERE policy_base = @policy_base

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return 10
END

FETCH NEXT FROM c1
INTO @policy_base

END

CLOSE c1
DEALLOCATE c1

update policy
set total_premium = pure_premium + (
select sum(fee_amount)
from policy_transaction_fee b
where a.policy_base = b.policy_base
and a.policy_suffix = b.policy_suffix
),
installment_fees = (
select sum(fee_amount)
from policy_transaction_fee b
where a.policy_base = b.policy_base
and a.policy_suffix = b.policy_suffix
and fee_code in ('INS', 'INF', 'EINS', 'EINF')
),
insured_balance = (
select sum(insured_balance)
from policy_transaction b
where a.policy_base = b.policy_base
and a.policy_suffix = b.policy_suffix
)
from policy a,
@policy_transaction_temp x
where a.policy_base = x.policy_base
and a.policy_suffix = x.policy_suffix
and a.current_flag = 'Y'

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return 10
END

UPDATE apptable
SET override_flag = @current_override
WHERE company = 2
AND subcompany = 2
AND application_name = 'Policy'

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
return 10
END

ROLLBACK TRAN

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-06-05 : 09:32:54

I guess the issue is that it is not a stored procedure?

I have created @errorflag and checked it at the end to see whether to rollback or commit. Is this the best way to go about it?

BEGIN TRAN

DECLARE @errorflag int
SET @errorflag = 0

DECLARE @current_override char(1)

SELECT @current_override = override_flag
FROM apptable
WHERE company = 2
AND subcompany = 2
AND application_name = 'Policy'

IF @@ERROR <> 0
BEGIN
SET @errorflag = 1
END

UPDATE apptable
SET override_flag = 'y'
WHERE company = 2
AND subcompany = 2
AND application_name = 'Policy'

IF @@ERROR <> 0
BEGIN
SET @errorflag = 1
END

DECLARE @policy_transaction_temp table (
[Policy_Base] varchar(12)
,[Policy_Suffix] smallint
,[Sequence] int
,[Transaction_Type] char(2)
,[Effective_Date] datetime
,[Installment_Fee] smallmoney
,[Entered_Date] datetime
,[Endorsement] smallint
,[Company] char(1)
,[Subcompany] char(1)
)

INSERT @policy_transaction_temp (
[Policy_Base]
,[Policy_Suffix]
,[Sequence]
,[Transaction_Type]
,[Effective_Date]
,[Installment_Fee]
,[Entered_Date]
,[Endorsement]
,[Company]
,[Subcompany]
)
SELECT policy.policy_base --[Policy_Base]
,policy.policy_suffix --[Policy_Suffix]
,(
SELECT MAX(policy_transaction.sequence)
FROM policy_transaction
WHERE policy_transaction.policy_base = policy.policy_base
AND policy_transaction.policy_suffix = policy.policy_suffix
) + 1 AS sequence --[Sequence]
,'AD' --[Transaction_Type]
,getdate() --[Effective_Date]
,-10.00 --[Installment_Fee]
,getdate() --[Entered_Date]
,policy.endorsement --[Endorsement]
,policy.company --[Company]
,policy.subcompany --[Subcompany]
FROM policy_transaction
INNER JOIN policy
ON policy_transaction.policy_base = policy.policy_base
AND policy_transaction.policy_suffix = policy.policy_suffix
INNER JOIN policy_transaction_fee
ON policy_transaction.policy_base = policy_transaction_fee.policy_base
AND policy_transaction.policy_suffix = policy_transaction_fee.policy_suffix
WHERE (policy_transaction.entered_date >= '2008-08-28')
AND (policy.status <> 'ca')
AND (policy.eft_flag = 'y')
AND (policy.current_flag = 'y')
AND (policy.installment_fees >= 10)
AND (policy_transaction_fee.fee_code = 'ins')
AND cast(policy.policy_base as varchar(100)) + '_' + cast(policy.policy_suffix as varchar(1)) IN (
SELECT cast(policy_transaction_fee.policy_base as varchar(100)) + '_' + cast(policy_transaction_fee.policy_suffix as varchar(1))
FROM policy_transaction_fee
WHERE (fee_code = 'ins')
GROUP BY cast(policy_transaction_fee.policy_base as varchar(100)) + '_' + cast(policy_transaction_fee.policy_suffix as varchar(1))
HAVING (SUM(fee_amount) > 0)
)

GROUP BY policy.policy_base,
policy.policy_suffix,
policy.endorsement,
policy.company,
policy.subcompany

IF @@ERROR <> 0
BEGIN
SET @errorflag = 1
END

DECLARE @policy_base varchar(12)

DECLARE c1 CURSOR READ_ONLY
FOR
SELECT policy_base
FROM @policy_transaction_temp

OPEN c1

FETCH NEXT FROM c1
INTO @policy_base

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @insured_balance smallmoney
DECLARE @installment_balance smallmoney

SELECT @insured_balance = Policy.Insured_Balance
,@installment_balance = Policy_Information.Installment_Balance
FROM Policy_Information
INNER JOIN Policy
ON Policy_Information.Policy_Base = Policy.Policy_Base
AND Policy_Information.Policy_Suffix = Policy.Policy_Suffix
WHERE Policy.current_flag = 'Y'
AND Policy_Information.Policy_Base = @policy_base

IF @@ERROR <> 0
BEGIN
SET @errorflag = 1
END

INSERT policy_transaction (
[Policy_Base]
,[Policy_Suffix]
,[Insured_Balance]
,[Installment_Balance]
,[Sequence]
,[Transaction_Type]
,[Effective_Date]
,[Entered_Date]
,[Endorsement]
,[Company]
,[Subcompany]
)
SELECT [Policy_Base]
,[Policy_Suffix]
,CASE
WHEN ( @insured_balance > 0 OR @installment_balance = 0 ) THEN -10
ELSE 0
END
,CASE
WHEN ( @insured_balance = 0 AND @installment_balance > 0 ) THEN -10
ELSE 0
END
,[Sequence]
,[Transaction_Type]
,[Effective_Date]
,[Entered_Date]
,[Endorsement]
,[Company]
,[Subcompany]
FROM @policy_transaction_temp
WHERE policy_base = @policy_base

IF @@ERROR <> 0
BEGIN
SET @errorflag = 1
END

DECLARE @next_sequence INT

SELECT @next_sequence = (MAX(policy_transaction_fee.sequence)+1)
FROM policy_transaction_fee
WHERE policy_base = @policy_base

IF @@ERROR <> 0
BEGIN
SET @errorflag = 1
END

INSERT policy_transaction_fee (
[Policy_Base]
,[Policy_Suffix]
,[Sequence]
,[fee_code]
,[fee_amount]
,[entered_by]
,[entered_date]
)
SELECT [Policy_Base]
,[Policy_Suffix]
,@next_sequence
,'INS '
,[Installment_Fee]
,8000
,[Entered_Date]
FROM @policy_transaction_temp
WHERE policy_base = @policy_base

IF @@ERROR <> 0
BEGIN
SET @errorflag = 1
END

FETCH NEXT FROM c1
INTO @policy_base

END

CLOSE c1
DEALLOCATE c1

update policy
set total_premium = pure_premium + (
select sum(fee_amount)
from policy_transaction_fee b
where a.policy_base = b.policy_base
and a.policy_suffix = b.policy_suffix
),
installment_fees = (
select sum(fee_amount)
from policy_transaction_fee b
where a.policy_base = b.policy_base
and a.policy_suffix = b.policy_suffix
and fee_code in ('INS', 'INF', 'EINS', 'EINF')
),
insured_balance = (
select sum(insured_balance)
from policy_transaction b
where a.policy_base = b.policy_base
and a.policy_suffix = b.policy_suffix
)
from policy a,
@policy_transaction_temp x
where a.policy_base = x.policy_base
and a.policy_suffix = x.policy_suffix
and a.current_flag = 'Y'

IF @@ERROR <> 0
BEGIN
SET @errorflag = 1
END

UPDATE apptable
SET override_flag = @current_override
WHERE company = 2
AND subcompany = 2
AND application_name = 'Policy'

IF @@ERROR <> 0
BEGIN
SET @errorflag = 1
END

IF @errorflag = 1
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT
END

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-06-05 : 12:07:30

EDIT: Didn't realize what forum I was in. Have you looked into using a TRY-CATCH block?

Either, you need/should implement procedural logic, as in checking the @ErrorFlag before each Insert/Update/etc so you are not doing data manipulation that you'd have to rollback later. Or, when you set the @ErrorFlag to 1, use a GOTO statement to jump to the end and rollback your transaction.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-05 : 13:01:11

I didn't attempt to figure out what your code is doing but in case you didn't know:
If this is not an SP but rather just a t-sql batch if you remove the "10" from the return statement that will eliminate the errors you were getting. In the context of an SP you can return an integer value which can be read from the calling procedure. But returning an integer outside the context of an SP doesn't make sense so you can just remove the "10" but keep the "return".

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources