Need to write a Stored Proceduer

• Mouli

  Right there with Babe

  Points: 735

  More actions

  November 15, 2002 at 6:25 am

  #158995

  I needs to write a Stored Procedure which inserts a single record

  into a hearder table and a set of records into the detail table.The link

  between the header and detail table is a running number(ex:Bill No).

  The records for the detail table will be fetched from many tables across the database.

  Do I need to put the records in a temporary table or can I construct a string

  or do i need to construct a Cursor or any better way Please suggest.

  Thanks

• Klaas-Jan

  SSCrazy

  Points: 2297

  More actions

  November 15, 2002 at 6:45 am

  #441472

  i think something like this is what you want.

  --insert header row

  insert into tblheader (col1,col2,billno)values (val1,val2,billnoval)

  --decalre varto hold billnumber

  declare @billno integer

  -- get bill number

  set @billno = (select billno from tblheader where col1=val1 and col2=val2)

  --insert details

  insert into tblDetail (billno,col1,col2)

  select @billno,col1,col2

  from tblx x

  inner join tbly y on x.id=y.id

  inner join tblz z on y.id=z.id

• Mkumari

  Hall of Fame

  Points: 3498

  More actions

  November 15, 2002 at 8:38 am

  #441492

  Hope this is what you are looking for.

  Insert INTO Header(BillID)

  SELECT Column1 FROM Table1 WHERE ID = @ID

  INSERT INTO Detail(Columns)

  SELECT Columns FROM Table1 INNER JOIN Table2

  ON Table1.ID = Table2.ID......

• Mouli

  Right there with Babe

  Points: 735

  More actions

  November 18, 2002 at 6:34 am

  #441609

  The first option meets my requirement.

  Thanks for the suggestions.