Multiple table value insertion with single Stored procedure

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • hellboss
    New Member
    • Jun 2007
    • 50

    #1

    Multiple table value insertion with single Stored procedure

    Hi Every one !

    I need to insert a set of values which are passed thru the parameter ,Below is the code to do the Single table insertion , Now I need to do multiple table insertion ie, to insert values with the addtional parameter in another table but using the same stored procedure !!

    CREATE procedure [dbo].[Pro_SIDN]
    @SID int,
    @FirstName varchar(10),
    @MiddleName varchar(10),
    @LastName varchar(10),
    @FullName varchar(10),
    insert into tableA(SID,Firs tname,Middlenam e,Lastname,Full name)
    values (@SID,@FirstNam e,@MiddleName,@ LastName,@FullN ame)

    Kindly provide me with some sample code ASAP !
    Thanks in advance !
  • Motoma
    Recognized Expert Specialist
    • Jan 2007
    • 3236

    #2
    Please do not post questions to the Articles section of the site. I have moved your question to the appropriate forum; hopefully you will find more help there.

    Comment

    • Infide
      New Member
      • Jul 2007
      • 28

      #3
      Originally posted by hellboss
      Hi Every one !

      I need to insert a set of values which are passed thru the parameter ,Below is the code to do the Single table insertion , Now I need to do multiple table insertion ie, to insert values with the addtional parameter in another table but using the same stored procedure !!

      CREATE procedure [dbo].[Pro_SIDN]
      @SID int,
      @FirstName varchar(10),
      @MiddleName varchar(10),
      @LastName varchar(10),
      @FullName varchar(10),
      insert into tableA(SID,Firs tname,Middlenam e,Lastname,Full name)
      values (@SID,@FirstNam e,@MiddleName,@ LastName,@FullN ame)

      Kindly provide me with some sample code ASAP !
      Thanks in advance !
      I'm not exactly sure what you're trying to accomplish. But this may get you started.

      Code:
      create proc usp_Example
      (@Value1 varchar(50),@Value2 varchar(50))
      AS
      BEGIN
      
      DECLARE @ParentTablePrimaryKey INT
      
      Insert into ParentTable(Column1)
      values(value1)
      
      set @ParentTablePrimaryKey = @@IDENTITY
      
      Insert into ChildTable(Column1,fk_ParentTableID)
      values(@value2,@ParentTablePrimaryKey)
      
      END
      You can do as many selects, inserts, deletes and updates as you want in a stored proc. I've seen stored procs over 1000 lines long. If you are working with a relational database with identity columns you will need to grab the identity ID that is auto generated when you insert with the @@Identity.

      I think this is what you are going after. If not please provide us with some more details.

      Comment

      Working...