How to Pass One SP’s Result as Another SP’s Parameter.

• lucyoura 86267

  SSC Veteran

  Points: 203

  More actions

  December 1, 2016 at 10:53 am

  #149383

  I have this situation:

  create proc AA

  as

  begin

  exec proc B @name

  if @cep > 1000

  select table1000

  else

  select table1001

  go

  create proc BB

  @name varchar(50)

  as

  select @cep = cep from table where name = @name

  go

  How can I get value of @cep to use it at proc AA?

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  December 1, 2016 at 11:55 am

  #1915540

  You can use the INSERT... EXEC... construct to save the results of stored proc BB into a temp table, then read from that. You'll need the @name parameter to pass into BB though, your sample code didn't show where that came from.

  Something like:

  create proc BB

  @name varchar(50)

  as

  select cep from table where name = @name

  go

  create proc AA

  as

  begin

  create table #result (cep int)

  insert into #result exec BB @name

  select top 1 @cep = cep from #result

  if @cep > 1000

  select table1000

  else

  select table1001

  drop table #result

  end

  go

  https://technet.microsoft.com/en-us/library/aa175921(v=sql.80).aspx