Re: call & return value from a sproc w/i another sproc

• AJ-92543

  SSC Eights!

  Points: 852

  More actions

  April 22, 2005 at 11:24 am

  #362416

  Hi,

  I would like to know if you can call a stored procedure(sp2) w/i another stored procedure(sp1) and return a value to the calling sproc(sp1).

  like

  Create procedure dbo.sp1

          @material_lotnum char(20),

   @insert_lotnum char(20),

   @cure_date char(8),

          @lotnum int out

  select @CT= count(lotnum)

   from tblFinal_inspection

   where apple_part_num = @apple_part_num and material_lotnum = @material_lotnum

   and cure_date = @cure_date and fi_closed = 0 and @insert_lotnum is null and fi_closed = 0;

   

  if @@rowcount = 1

  select @lotnum = lotnum

   from tblFinal_inspection

   where apple_part_num = @apple_part_num and material_lotnum = @material_lotnum

   and cure_date = @cure_date and fi_closed = 0 and @insert_lotnum is null and fi_closed = 0;

  Else

  --call sp2

  --return lotnum from sp2

  Thanks!

• ron k

  SSCertifiable

  Points: 6103

  More actions

  April 22, 2005 at 12:13 pm

  #554404

  You can return values by using an "output" paramenter or by capturing the value returned by the "Return" statement.

  Its well documented in BOL.

   

• Rob Alexander

  SSC-Addicted

  Points: 484

  More actions

  April 26, 2005 at 12:42 pm

  #554997

  You can also simply return int pass/fail codes like follows:

  create procedure myproc ()

  declare @err int

  begin

  set @err = 1

  return (@err)

  end

  sample call:

  declare @return int

  exec @return = myproc