Stored Procedure Problem

[mrDrive]

Hi,

I have a (relatively) simple stored procedure that I'm executing from a Java class.

CREATE PROCEDURE [dbo].[AddNewUser]
@NewContrID int, 
@Name varchar(100), 
@Addr varchar(200), 
@City varchar(200),
@St varchar(20),
@Zip varchar(50),
@NewUserID int,
@UserName varchar(25),
@Pwd varchar(25),
@Email varchar(50),
@AccessLevel bit,
@Co varchar(200)
AS
SET @NewContrID = (SELECT (MAX(CONTRACTOR_ID) + 1) AS NewValue FROM dbo.CONTRACTORS)
SET @NewUserID = (SELECT (MAX(USER_ID) + 1) AS NewValue FROM dbo.SYSUSERS)
RETURN @NewContrID
RETURN @NewUserID
INSERT INTO dbo.CONTRACTORS VALUES(@NewContrID, @Name, @Addr, @City, @St, @Zip, NULL)
INSERT INTO dbo.SYSUSERS VALUES(@NewUserID, @UserName, @Name, @Pwd, @Email, @AccessLevel, 0, 0, @Co, @NewContrID)
GO

All the params are going into the procedure call correctly but no record is added. When I go to Enterprise Mgr and run the procedure call, I get the "1 row affected" message, but no new record is added to my tables.

Any help would be greatly appreciated!

-mD

 

[vongrunt]

Sproc exits unconditionally on first RETURN...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mrdenny]

The return statements are stopping your procedure in it's tracks.

You should only have one return statement as that's the last thing that will be run. Nothing beyond that will be executed.

What result are you trying to get from the return statements.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[mrDrive]

Ahh. I'm trying to create new primary key values for each table then inject them into my insert statements.

 

[mrDrive]

Cool. Does what I'm looking for without the "return" lines.

Thanks for the help!

 

[mrdenny]

in that case the return lines are totally unneeded as you've noticed.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)