stored procedure for inserting im multiple tables

Results 1 to 2 of 2

Thread: stored procedure for inserting im multiple tables

  1. #1
    Join Date
    Dec 1969

    Default stored procedure for inserting im multiple tables

    Here is what I have to do. <BR>I have to insert some values in the first table then capture the ID of that table and insert a record in another table where the captured ID is also one ofthe fields to be inserted.<BR>How do I write a stroredprocedure for it??<BR>I have done so far..I am stuck here...plse help me <BR>insert into Course_Info (CourseID,CourseInfo) Values (@courseID,@courseInfo)<BR>insert into Course_Layout(CourseID, No_of_holes,Pro,Architect,Season,Dress_code,Teetim e_policy,Features,Driving_range)<BR>Values(@Course ID,@Holes,@Pro,@Designer,@Season,@Dresscode,@polic y,@Features,@range)<BR>insert into Course_layout_rating(Layout,Blue_rating) Values(??)<BR>The layout value is the ID generated in the Course_layout table when we insert record in Course_layout table.<BR><BR>Somebody help me thanks!!

  2. #2
    Join Date
    Dec 1969

    Default RE: stored procedure for inserting im multiple tab

    Once you insert a record use the @@identity to capture that identity value.<BR><BR>e.g.<BR>____________________________ ___<BR>create proc MyProcName<BR> @data1 int,<BR> @data2 int,<BR> @data3 int,<BR> @data4 int,<BR> @data5 int,<BR> @data6 int<BR>as<BR><BR>Declare @this_id int; /* assuming your id field is int type */<BR><BR>insert into [table1]<BR>(field1, field2, field3)<BR>values<BR>(@data1, @data2, @data3);<BR>select @this_id = @@identity;<BR><BR>insert into [table2]<BR>(field1, field2, field3, field4)<BR>values<BR>(@this_id, @data4, @data5, @data6);<BR><BR>RETURN(0) /* done return exit code 0 */<BR>__________________________________<BR><BR>Ha ve a go at this. Hope it helps,<BR><BR>Reginald<BR>

Posting Permissions

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