Returning a value from a stored procedure when also using a transaction

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 2000 Forums

SQL Server Development (2000)

Returning a value from a stored procedure when also using a transaction

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-09 : 10:30:19

john writes "Is is possible to pass an integer back from a sproc using the "RETURN" statement when you are using a transaction? When running the sproc below I get the following error: "A RETURN statement with a return value cannot be used in this context." but a simple "RETURN" runs.

This seems like a basic feature but SQL Server 2K doesn't seem to support it.

Any assistance is greatly appreciated!

CREATE PROCEDURE qryInsertUser_NWP
@lngSessionID int,
@txtUserName varchar(20),
@txtPassword varchar(20),
@lngLanguage int,
@txtAgencyName varchar(50)=NULL,
@txtFirstName varchar(25),
@txtLastName varchar(25),
@txtLicense varchar(25)=NULL,
@txtDesignation varchar(25)=NULL,
@txtAddress1 varchar(35),
@txtAddress2 varchar(35)=NULL,
@txtCity varchar(15),
@txtAdminNote text=NULL,
@lngState int,
@txtZip varchar(10),
@txtPhone varchar(25)=NULL,
@txtFax varchar(25)=NULL,
@txtEmail varchar(100)=NULL,
@txtAgentNumber varchar(15)=NULL

AS

DECLARE @Initials varchar(2),
@NewUserID INT,@FldID INT

DECLARE @Error_User INT
DECLARE @Error_Agent INT
DECLARE @Error_Role INT
DECLARE @RetError INT

SELECT @Initials = UPPER(LEFT(tblAgents.txtFirstName,1)) + UPPER(LEFT(tblAgents.txtLastName,1))
FROM tblUserSessions
INNER JOIN tblUsers
ON tblUserSessions.lngUser = tblUsers.autUserID
INNER JOIN tblAgents
ON tblUsers.autUserID = tblAgents.FK_lngUser
WHERE tblUserSessions.lngSessionID = @lngSessionID

BEGIN TRAN

INSERT INTO tblUsers
(txtUserName, txtPassword, txtAdminNote,FK_lngLanguage, dtiModified, txtByWho)
VALUES
(@txtUserName, @txtPassword,@txtAdminNote, @lngLanguage, getDate(), @Initials)

SELECT @Error_User = @@error

SELECT @NewUserID=@@Identity

INSERT INTO tblAgents
(FK_lngUser,txtAgencyName,txtFirstName,txtLastName,txtLicense,txtDesignation,txtAddress1,txtAddress2,txtCity,
FK_lngState,txtZip,txtPhone,txtFax,txtEmail,txtAgentNumber)
VALUES
(@NewUserID,@txtAgencyName,@txtFirstName,@txtLastName,@txtLicense,@txtDesignation,@txtAddress1,@txtAddress2,@txtCity,
@lngState,@txtZip,@txtPhone,@txtFax,@txtEmail,@txtAgentNumber)

SELECT @Error_Agent = @@error

--Add the user as a field agent to the roles table
--Get the ID for the field agent role
SELECT @FldID=tblRoles.PK_autRolesID from tblRoles WHERE txtRoleCode='FLD'
INSERT INTO tblUserRoleLinks (FK_lngUser,FK_lngRole) VALUES (@NewUserID,@FldID)

SELECT @Error_Role = @@error

IF ((@Error_User <> 0 )AND (@Error_Agent <> 0) AND (@Error_Role <> 0))
BEGIN
COMMIT TRAN
SET @RetError = 0
--RETURN 0
END
ELSE
BEGIN
SET @RetError = 1
ROLLBACK TRAN
--RETURN 1
END

RETURN @RetError"

motokevin
Starting Member

36 Posts

Posted - 2002-05-09 : 12:13:23

I'm sure there are different ways to do it depending on which application you want to return the value to. But I use VB and ASP alot and the best way I know to do what you want is to return a recordset.

Simply change the

quote:
IF ((@Error_User <> 0 )AND (@Error_Agent <> 0) AND (@Error_Role <> 0))
BEGIN
COMMIT TRAN
SET @RetError = 0
--RETURN 0
END
ELSE
BEGIN
SET @RetError = 1
ROLLBACK TRAN
--RETURN 1
END

to instead say

IF ((@Error_User <> 0 )AND (@Error_Agent <> 0) AND (@Error_Role <> 0))
BEGIN
COMMIT TRAN
SELECT 0 As ReturnError
--RETURN 0
END
ELSE
BEGIN
ROLLBACK TRAN
SELECT 1 As ReturnError
--RETURN 1
END

and get rid of the RETURN statements

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources