asked on

ROLLBACK TRANSACTION and end stored procedure

Hello, I can't seem to find the right SQL to rollback a transaction and end the stored procedure returning a value from a SELECT to alert the program that the stored procedure failed and there was a rollback.

I'm affraid the code I have below won't return a value for the program to use for a failure.

Here is the code I have so far:

----------------------------------------------------------------------------

CREATE PROCEDURE sp_locator_update
AS
SET NOCOUNT ON
DECLARE @err int
BEGIN TRANSACTION

--....... LOTS MORE CODE .......

--- Next: back up existing sbma_old, deptname_old, volunteer_old,
--- supervisor_old, deptdescript_old ----

DROP TABLE sbma_old
SELECT * INTO sbma_old FROM sbma

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END

DROP TABLE deptname_old
SELECT * INTO deptname_old FROM deptname

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END

DROP TABLE volunteer_old
SELECT * INTO volunteer_old FROM volunteer

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END

DROP TABLE Supervisor_old
SELECT * INTO Supervisor_old FROM Supervisor

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END

DROP TABLE deptdescript_old
SELECT * INTO deptdescript_old FROM deptdescript

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END

--- Update Locator Tables sbma, deptname, volunteer,
--- supervisor, deptdescript ----

DELETE FROM sbma
INSERT INTO sbma
(Filenum,ssn,lname,fname,mname,DeptNum,Location,deptname,
slname,sfname,smname,PhoneLocal,PhoneExternal,PhoneExternalExt,PhoneLocal2,
PhoneExternal2,PhoneLocal3,PhoneExternal3,mailcode,faxexternal,faxlocal,
email,EXemail,GWemail,Pager,CellPhone,jobtitle,deptkey,locstate,
locnum,locationaddress1,locationaddress2,locationcity,locationpostalcode,
branch,old_lname,old_lname_delete)
SELECT
Filenum,ssn,lname,fname,mname,DeptNum,Location,deptname,
slname,sfname,smname,PhoneLocal,PhoneExternal,PhoneExternalExt,PhoneLocal2,
PhoneExternal2,PhoneLocal3,PhoneExternal3,mailcode,faxexternal,faxlocal,
email,EXemail,GWemail,Pager,CellPhone,jobtitle,deptkey,locstate,
locnum,locationaddress1,locationaddress2,locationcity,locationpostalcode,
branch,old_lname,old_lname_delete
FROM #sbma

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END

DELETE FROM deptname
INSERT INTO deptname
(DepartmentID,DepartmentName,Location,slname,sfname,smname,MailCode,
FaxLocal,FaxExternal,DeptHead,deptPhoneLocal,deptPhoneExternal,deptbuzz,
deptdesc,specialized,deptkey,deptkey2,Region,Market,Branch)
SELECT
DepartmentID,DepartmentName,Location,slname,sfname,smname,MailCode,
FaxLocal,FaxExternal,DeptHead,deptPhoneLocal,deptPhoneExternal,deptbuzz,
deptdesc,specialized,deptkey,deptkey2,Region,Market,Branch
FROM #deptname

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END

DELETE FROM volunteer
DBCC CHECKIDENT('volunteer', RESEED, 0)
INSERT INTO volunteer
(filenum,lname,fname,abbvr,lang_name,nlanguage,
ntranslator,nteacher,nspeak,nread,nwrite,sdate)
SELECT
filenum,lname,fname,abbvr,lang_name,nlanguage,
ntranslator,nteacher,nspeak,nread,nwrite,sdate
FROM #volunteer

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END

DELETE FROM Supervisor
DBCC CHECKIDENT('Supervisor', RESEED, 0)
INSERT INTO Supervisor
(NetworkID,slname,sfname)
SELECT
[network id] AS NetworkID,slname,sfname
FROM #supervisor

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END

DELETE FROM deptdescript
INSERT INTO deptdescript
(DepartmentID,DepartmentName,deptbuzz,deptdesc,contactname,contactphone,updatedate)
SELECT
DepartmentID,DepartmentName,deptbuzz,deptdesc,contactname,contactphone,updatedate
FROM #deptdescript

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END

COMMIT TRANSACTION

SELECT @err AS ERROR

GO

----------------------------------------------------------------------------

Jokra_the_Barbarian

Maybe try:

IF @@error<> 0
BEGIN
ROLLBACK TRANSACTION
SELECT @err = @@error
END

Scott Pletcher

This command will "work":

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION RETURN @err END

but it will not do the same thing as this command:

SELECT @err AS ERROR

It looks as it you want a *result set* returned not just a *return code*. If so, change the error coding to:

SELECT @err = @@error IF @err <> 0 BEGIN ROLLBACK TRANSACTION SELECT @err RETURN @err END

That will both set the return code *and* return a *result set*.

Scott Pletcher

That is:

RETURN @err -- or other value

sets a return code that the calling code can check, but it will not produce a result set, so if the calling code is basing things on a result set, there won't be one.

In T-SQL, you can check for a return code like this:

DECLARE @rc INT

EXEC @rc = sp_locator_update

@rc will then contain the RETURN value (@err in this case).

You can also check the RC in VB, etc., but, sorry, I don't know the details of how to do that.

HangTenDesign

ASKER

Jokra_the_Barbarian , Yes that would roll back the transaction and select the value but the stored procedure would continue and run the remaining statements. I need to exit the stored procedure but still return the select statement for the program to handle.

HangTenDesign

ASKER

ScottPletcher, are you saying your first answer won't work? I am using VB to call the procedure but am looking for a result set. This may be impossible?

HangTenDesign

ASKER

Do you think I may have to put every singe satatement within an IF statement? That is, only run the statements if the @err = 0?

ASKER CERTIFIED SOLUTION

Jokra_the_Barbarian

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

HangTenDesign

ASKER

A GOTO would jump over everything else and not run them? This may work let me try it out. This would save me a lot of coding.

Jokra_the_Barbarian

Yes, look at the 3rd code example on this page: http://www.sqlteam.com/item.asp?ItemID=3850

I use the GOTO statement all the time in Access VBA for error handling.

HangTenDesign

ASKER

This is AWESOME. Thank you!

-------------------------------------------------------

DECLARE @test int
DECLARE @err int
SET @test = 1

SELECT @err = @test IF @err <> 0 BEGIN /*ROLLBACK TRANSACTION*/ GOTO error END

--to be skipped if error
select 'one'
select 'two'
select 'three'
select 'four'
select 'five'
select 'six'

error:
SELECT @err As ERROR

-------------------------------------------------------