Stored Procedure

• tripri

  SSCommitted

  Points: 1584

  More actions

  October 14, 2011 at 10:39 am

  #246485

  Hi I got two table

  tb1 and tb2

  tb1 has two columns

  part (Identity column) and value

  tb2 has two columns

  range and parfk

  I created two stored procedures which are below.

  Whta should happen is when i run stored procedure 2 it should insert into tb1 and get the identity value inserted into tb1 by that run and insert that into tb2

  Create Sp1

  @Value int,

  @Part int output

  As

  insert into tb1

  Values(@value)

  set @part=scope_Identity()

  Now i want to use that @part in another procedure like

  Create Sp2

  @range int

  As

  Declare @parfk int

  exec sp1 @value, @part output

  SET @parFk=@part(This is what i'm missing how can i pass that @part

  output value of sp1 into @parfk)

  insert into tb2

  values(@range,@parfk)

• ghanshyam.kundu

  Hall of Fame

  Points: 3337

  More actions

  October 14, 2011 at 10:58 am

  #1394783

  hi i would like to mention some point at here

  you can do this through single procedure why you want two procedure for that.

  you are not following proper naming convention (partfk)

  here is the solution which will work in your code,but think on it you can make it into a single one.

  CREATE table tmp1(part bigint Identity(1,1) primary key,value int)

  CREATE table tmp2(range nvarchar(200),partfk bigint references tmp1(part))

  select *from tmp1

  select *from tmp2

  go

  create procedure Sp1

  @Value int,

  @Part int output

  As

  insert into tmp1

  Values(@value)

  set @part=scope_Identity()

  go

  create procedure Sp2

  @range nvarchar(20)

  As

  Declare @parfk int

  DECLARE @Value int

  SET @value=34

  exec Sp1 @value, @parfk output

  SET @parFk=@parfk

  insert into tmp2

  values(@range,@parfk)

  EXEC sp2 'range'

  SELECT *from tmp1

  SELECT *from tmp2

  cheers....