CODEWITHSUNDEEP
sqlsql-server-2008stored-proceduresreturn
JackofAll
JackofAll

Reputation: 537

Stored Procedure Issue - Can't get both return values

I have a stored procedure as seen below:

ALTER PROCEDURE [dbo].[CreateNewLeague]
    -- Add the parameters for the stored procedure here
    @UserId uniqueidentifier,
    @LeagueName VARCHAR(256),
    @leagueId Int OUTPUT,
    @teamId Int OUTPUT

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT @teamId = [teamID] FROM [UserTeam] WHERE (userID = @UserId)

    RETURN @teamID
END

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO League([leagueAdminID], [leagueName]) VALUES (@UserId, @LeagueName)
    SELECT SCOPE_IDENTITY()

    Set @leagueId = SCOPE_IDENTITY()

    RETURN @leagueId
END



BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO LeagueTeam([leagueID], [teamID]) VALUES (@leagueId, @teamID)

END

My Issue is that I cannot get both return values when I execute the SPROC, I only ever get the return value of the one I put first. They both work when they are put first, but the second returns a value of NULL.

Any ideas what I am doing wrong?

Thanks in advance

Upvotes: 0

Views: 139

Answers (3)

Madhabendra
Madhabendra

Reputation: 36

SET the value instead of return..

Upvotes: 0

Nayan Shah
Nayan Shah

Reputation: 1

if you are using the output parameter then do not use the return only set the variables. ex. SET @teamID = 10

Upvotes: 0

Hans Kesting
Hans Kesting

Reputation: 39283

"Return" stops executing the stored procedure, so the second part is never executed. Leave out the "return" statements and read the output vars, you are already setting their values.

Upvotes: 1

Related Questions