stored procedure using transactions

[mp89]

I'm trying to write my first stored procedure using transactions. First of all, can anybody tell me if I am going about it the right way, and secondly, if I need to return the result of the transaction to the calling application (C# in this case) would I just declare an output parameter and set it to the value of @@ERROR?

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)
)

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

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

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

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
ROLLBACK TRAN
END
GO


Cheers,

Mike

 

[SQLSister]

YOu need to check for an error after each update. If one of the above failed but not the last one, @@error would show no error the way this is written.

Questions about posting. See faq183-874

 

[mp89]

So this should do the job then?


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)
)

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 @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

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

 

[mp89]

I tried this but it doesn't work :

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 @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
@RESULT = 1

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


Cheers,

Mike

 

[mp89]

Sorry, was missing SET from where I am setting my variables.