transaction question

[mp89]

Can anybody tell me what is wrong with my stored procedure (it's the first one I've written using transactions so I'm not 100% sure of what I'm doing) :

CREATE PROCEDURE DatabaseUpdateDirectoryChange
(
@DIVID INT,
@CUSTNAME VARCHAR(30),
@LISTDIRECTORY INT,
@ADD1 VARCHAR(50),
@ADD2 VARCHAR(50),
@ADD3 VARCHAR(50),
@CITY VARCHAR(50),
@COUNTY VARCHAR(50),
@ZIP VARCHAR(10),
@COUNTRY VARCHAR(50),
@RESULT BIT OUTPUT
)

AS

DECLARE @intErrorCode INT

BEGIN TRAN

UPDATE DIVISION
SET DIV_NAME =
CASE @CUSTNAME
WHEN '' THEN DIV_NAME
ELSE @CUSTNAME
END,
EXDIRECTORY =
CASE @LISTDIRECTORY
WHEN 0 THEN EXDIRECTORY
WHEN 1 THEN 1
WHEN 2 THEN 0
END
WHERE DIV_ID = @DIVID

--select command is the same as the set command when assigning values to variables
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM

UPDATE ADDRESS
SET ADD_LINE_1 =
CASE @ADD1
WHEN '' THEN ADD_LINE_1
ELSE @ADD1
END,
ADD_LINE_2 =
CASE @ADD2
WHEN '' THEN ADD_LINE_2
ELSE @ADD2
END,
ADD_LINE_3 =
CASE @ADD3
WHEN '' THEN ADD_LINE_3
ELSE @ADD3
END,
TOWN =
CASE @CITY
WHEN '' THEN TOWN
ELSE @CITY
END,
COUNTY =
CASE @COUNTY
WHEN '' THEN COUNTY
ELSE @COUNTY
END,
POSTCODE =
CASE @ZIP
WHEN '' THEN POSTCODE
ELSE @ZIP
END,
COUNTRY =
CASE @COUNTRY
WHEN '' THEN COUNTRY
ELSE @COUNTRY
END
WHERE DIV_ID = @DIVID

SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM

UPDATE CONTACTS
SET CONTACT_NAME =
CASE @CUSTNAME
WHEN '' THEN CONTACT_NAME
ELSE @CUSTNAME
END
WHERE DIV_ID = @DIVID

SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN
SET @RESULT = 1

PROBLEM:
IF (@intErrorCode <> 0)
BEGIN
ROLLBACK TRAN
SET @RESULT = 0
RETURN
END
GO


Cheers,

Mike

 

[hmckillop]

I think you are missing a RETURN statement just after the COMMIT TRAN.
This will terminate the proc at this point without then continuing and trying to execute the "Problem" section

"I'm living so far beyond my income that we may almost be said to be living apart

 

[mp89]

I've tried that and I get the same rsult. I'm wondering how people actually test the transactions in a stored procedure? I just changed the name of the table I was writing to to an invalid name to make it fail and rollback, but it just seems to lock up the table.


Regards,

Mike

 

[SQLBill]

What error(s) are you getting? Or what's happening or failing to happen?

-SQLBill

 

[mp89]

Bill,

When I run the stored procedure with valid data it runs to the Commit statement and works fine. So what I wanted to do, not having used transactions in SQL Server before was to check that the rollbacks work too - I changed the name of one of the tables in my stored procedure to an invalid table name, expecting it to rollback. But instead I get an error in my C# saying 'Transaction Count after EXECUTE indicates that a COMMIT or ROLLBACK statement is missing', and the relevant record in the table locks up.

Any ideas? Should I be used a different way to test my stored procedure, or is there something wrong with the way it is written?


Cheers,

Mike

 

[mp89]

By the way, I've made a few changes to the sproc since my first post. This is what I'm now working with :

CREATE PROCEDURE DatabaseUpdateDirectoryChange
(
@DIVID INT,
@CUSTNAME VARCHAR(30),
@LISTDIRECTORY INT,
@ADD1 VARCHAR(50),
@ADD2 VARCHAR(50),
@ADD3 VARCHAR(50),
@CITY VARCHAR(50),
@COUNTY VARCHAR(50),
@ZIP VARCHAR(10),
@COUNTRY VARCHAR(50),
@RESULT BIT OUTPUT
)

AS

BEGIN TRAN

UPDATE DIVISION
SET DIV_NAME =
CASE @CUSTNAME
WHEN '' THEN DIV_NAME
ELSE @CUSTNAME
END,
EXDIRECTORY =
CASE @LISTDIRECTORY
WHEN 0 THEN EXDIRECTORY
WHEN 1 THEN 1
WHEN 2 THEN 0
END
WHERE DIV_ID = @DIVID

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
SET @RESULT = 0
RETURN @RESULT
END


UPDATE ADDRESS
SET ADD_LINE_1 =
CASE @ADD1
WHEN '' THEN ADD_LINE_1
ELSE @ADD1
END,
ADD_LINE_2 =
CASE @ADD2
WHEN '' THEN ADD_LINE_2
ELSE @ADD2
END,
ADD_LINE_3 =
CASE @ADD3
WHEN '' THEN ADD_LINE_3
ELSE @ADD3
END,
TOWN =
CASE @CITY
WHEN '' THEN TOWN
ELSE @CITY
END,
COUNTY =
CASE @COUNTY
WHEN '' THEN COUNTY
ELSE @COUNTY
END,
POSTCODE =
CASE @ZIP
WHEN '' THEN POSTCODE
ELSE @ZIP
END,
COUNTRY =
CASE @COUNTRY
WHEN '' THEN COUNTRY
ELSE @COUNTRY
END
WHERE DIV_ID = @DIVID

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
SET @RESULT = 0
RETURN @RESULT
END

UPDATE CONTACTS
SET CONTACT_NAME =
CASE @CUSTNAME
WHEN '' THEN CONTACT_NAME
ELSE @CUSTNAME
END
WHERE DIV_ID = @DIVID

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
SET @RESULT = 0
RETURN @RESULT
END

COMMIT TRAN
SET @RESULT = 1
RETURN @RESULT
GO

 

[SQLBill]

Maybe this explains it:

Reference: Inside Microsoft SQL Server 2000 by Kalen Delany.

From Error Checking in Transactions (pgs 660 and 661).

Just rolling back the transaction if an error is found is not sufficient, however, because in most cases a ROLLBACK TRAN does not change the flow of control in the execution of your statements. The ROLLBACK TRAN would undo any completed modifications in the current transaction and make the appropriate entries in the transaction log. However, program flow would continue to the next line.

In the example in the book, each error check has the line:
GOTO ON_ERROR
then after the COMMIT is:
ON_ERROR:
<code to do upon error>

So, the way I read it is, if the first part of your transaction fails, the error handling is done (rollback, update @RESULT), but then the transaction continues to the next statement. Is that what you really want? Or do you want it to 'break-out' of the transaction?

If you want it to break-out of the transaction, put a GOTO ON_ERROR line in. I think this would be what's needed:

Make all your error handling this:

IF @@ERROR <> 0
   BEGIN
    ROLLBACK TRAN
  [b]GOTO ON_ERROR[/b]            
   END

Then make this change:

COMMIT TRAN
SET @RESULT = 1
RETURN @RESULT

ON_ERROR:
  SET @RESULT = 0
  RETURN @RESULT

-SQLBill

 

[mp89]

Bill,

Your example gives exactly the same error as I was getting before. Maybe there is nothing wrong with my code and I should be approaching testing a different way?


Regards,

Mike