Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Egypt
    Posts
    12

    Unanswered: Output parameter of a stored procedure

    I have a stored procedure (named Insert_SRegister) that calls another
    stored procedure (named: Generate_Student_Code) and a value should be returned from the second to the first one.
    The program goes like this:
    The first stored procedure inserts info of students and every student should have his own id which is gentratead by the second stored
    procedure.

    The second sp which generates the id does its job well and the first sp which inserts the info does its job well too, the Problem occurs when the genrated ID is to retrurn from the second sp to the first. I do no not know why it refuses to be transmitted in a correct way it gives no error but the id inserted in the db is always zero and I am sure that the id is generated in correct way, the problem occurs only in the transmission;

    The code of the first procedur is :

    Create Procedure dbo.Insert_SRegister

    @YGroup VarChar (3),
    @YDate VarChar (4),
    @YTerm Char(1),
    @SName VarChar(30),
    @SSecondName VarChar(30),
    @SFirstName VarChar(30),
    @SGender TinyInt,
    @SBDate DateTime,
    @SBPlace VarChar(50),
    @SOCountry VarChar(50),
    @SOPassCount VarChar(50),
    @PassNo VarChar(50),
    @SOLang VarChar(50),
    @MOLang VarChar(50),


    @MName VarChar(50),
    @MHAdd VarChar(50),
    @MHT1 VarChar(20),
    @MHT2 VarChar(20),
    @MHT3 VarChar(20),
    @MHMob VarChar(20),
    @MWAdd VarChar(50),
    @MWT1 VarChar(20),
    @MWT2 VarChar(20),
    @MExt VarChar(10),
    @MWMob VarChar(20),


    @FName VarChar(50),
    @FHAdd VarChar(50),
    @FHT1 VarChar(20),
    @FHT2 VarChar(20),
    @FHT3 VarChar(20),
    @FHMob VarChar(20),
    @FWAdd VarChar(50),
    @FWT1 VarChar(20),
    @FWT2 VarChar(20),
    @FExt VarChar(10),
    @FWMob VarChar(20),


    @EAdd VarChar(50),
    @ETele VarChar(20),
    @SchName VarChar(50),
    @SAdd VarChar(50),

    @FBNo Smallint,
    @FSNo Smallint,
    @FONo VarChar(50),
    @EMedical VarChar(1000),
    @FDetails VarChar(1000),
    @FRDetails VarChar(1000),
    @PName VarChar(50),
    @StudentStatus VarChar(50) OUTPUT,
    @ID VarChar (50) OUTPUT

    As
    Begin
    Declare @Exists Int, -- Return Value
    @StudentCode VarChar(50)

    -----------------------------------------
    -- here I call the second proc and assgin it output to variable
    --@StudentCode
    -----------------------------------------------------------------------------------
    exec @StudentCode = dbo.Generate_Student_Code @YGroup,@YDate, @StudentCode OUTPUT

    ----Do work and insert info in db

    End



    Code of the second stored procedure is:
    ALTER Procedure dbo.Generate_Student_Code

    @YGroup VarChar (3),
    @YDate VarChar (4),
    @newID VarChar (50) OUTPUT
    As
    Begin
    set nocount on
    Declare @Exists int, -- Return Value
    @Mv varchar(5),
    @IdLen int
    If Exists(Select SID
    From SRegisteration)
    Begin
    select @Mv = CAST(CAST(Max(RIGHT(SID, 5))AS int) + 1 AS varchar(50)) From SRegisteration
    Set @IdLen = DATALENGTH(@Mv)

    Set @Mv =
    case
    when @IdLen = 1 then '0000' + @Mv
    when @IdLen = 2 then '000' + @Mv
    when @IdLen = 3 then '00' + @Mv
    when @IdLen = 4 then '0' + @Mv
    else @Mv
    end

    Set @newID = 'S' + '-' + @YGroup + '-' + @YDate + '-' + @Mv
    ---------Here I return the ID value
    select @newID
    --------------------------------------
    Select @Exists = 1

    End

    Else

    Begin
    Select @Exists = 0
    Set @newID = 'S' + '-' + @YGroup + '-' + @YDate + '-' + '00001'
    ---------Here I return the ID value
    select @newID
    ------------------
    Return @Exists
    End

    End

  2. #2
    Join Date
    Jan 2004
    Posts
    6

    Receiving output parameter

    I believe the issue is in the format that the second procedure is called in.

    exec @StudentCode = dbo.Generate_Student_Code @YGroup,@YDate, @StudentCode OUTPUT

    (exec @StudentCode = <Procedure...>) will set the @StudentCode variable equal to the value that is explicitly returned from the procedure (Return @Exists) or if none is explicitly returned, the result code of the procedure (which will be 0 if it succeeds).

    Code:
    DECLARE @StudentCodeOutput VarChar(50)
    
    exec @ReturnCode = dbo.Generate_Student_Code @YGroup,@YDate, @newID = @StudentCodeOutput OUTPUT
    The above code should set the @ReturnCode variable equal to the explicitly returned value and the @StudentCodeOutput variable equal to the value set for @newID within the second procedure.

  3. #3
    Join Date
    Feb 2004
    Location
    Egypt
    Posts
    12

    Talking

    Thank you very much for your help, it has worked

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •