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.
    ********************************************************/

  • Duplicate post. Please post any answers here

  • Sorry for 2 Posts first time site gave me some error so i entered it again, but it took both the 2 posts

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

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

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