A Simple procedure but no returning value

  • I have a procedure which shuld return an int depending on Insertion done but it is not returning any thing though i have written Return statement also

    Here is my Procedure

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    GO

    ALTER Procedure [dbo].[UserRoleMapping]

    @userProgrammeId int,@roleProgrammeId int,@createdBy int,@createdDate datetime,@loggedInAs int

    as

    begin

    declare @out int

    declare @return int

    set nocount on

    begin try

    begin tran UserRoleMapping

    Insert Into User_Roles(Fk_users,Fk_Roles,CreatedBy,CreatedDate,LoggedInAs)

    values(@userProgrammeId,@roleProgrammeId,@createdBy,@createdDate,@loggedInAs)

    commit tran UserRoleMapping

    set @return=1

    end try

    begin catch

    IF (XACT_STATE()) = -1

    BEGIN

    ROLLBACK TRAN UserRoleMapping

    set @return=0

    END

    ELSE IF (XACT_STATE()) = 1

    BEGIN

    --it now depends on the type of error or possibly the line number

    --of the error

    IF ERROR_NUMBER() = 547

    BEGIN

    ROLLBACK TRAN UserRoleMapping

    set @return=0

    END

    ELSE IF ERROR_NUMBER() = 2627

    BEGIN

    ROLLBACK TRAN UserRoleMapping

    set @return=0

    END

    END

    END CATCH

    return @return

    set nocount off

    end

    Can any one please tell me y it is not returning value as it is very urgent to me

    /********************************************************
    Technology is just a tool,in terms of getting the kids working together and motivating them,the teacher is the most important.
    ********************************************************/

  • Well, it depends on how you are calling it. If you are doing something like this:

    Exec [dbo].[UserRoleMapping] parameter list

    Then you will not get anything returned as this type of call is just looking for a result set to be returned and you are not returning a result set. If you want to get the return value which you are setting you need to do this:

    Declare @ReturnValue Int

    Exec @ReturnValue = [dbo].[UserRoleMapping] parameter list

    Select @ReturnValue

    If you are using .NET to call the procedure then you need to use a Parameter with a type of ReturnValue to get the return value.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply