?
Solved

ROLLBACK TRANSACTION and end stored procedure

Posted on 2005-04-26
10
Medium Priority
?
460 Views
Last Modified: 2008-03-04
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

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

0
Comment
Question by:HangTenDesign
  • 5
  • 3
  • 2
10 Comments
 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
ID: 13870436
Maybe try:

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

Expert Comment

by:Scott Pletcher
ID: 13870953
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*.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 13870974
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:HangTenDesign
ID: 13875155
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.
0
 

Author Comment

by:HangTenDesign
ID: 13875191
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?
0
 

Author Comment

by:HangTenDesign
ID: 13875281
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?
0
 
LVL 11

Accepted Solution

by:
Jokra_the_Barbarian earned 2000 total points
ID: 13875544
HangTen, why not use a GOTO? You could test for an error, then GOTO your error handling at end/bottom of the procedure.
0
 

Author Comment

by:HangTenDesign
ID: 13875664
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.
0
 
LVL 11

Expert Comment

by:Jokra_the_Barbarian
ID: 13875723
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.
0
 

Author Comment

by:HangTenDesign
ID: 13875828
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

-------------------------------------------------------
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question