Using Stored Procedures to INSERT Data

It is common to use both views and stored procedures to insert data into a database. However this can be tricky if you are inserting into multiple tables that use identity fields. In reality this is not so terribly difficult. Here are some examples that follow the student registration database example. You should have some familiarity with the the SQL INSERT.  

SQL Insert Stored Procedure Code

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[AddNewStudent]
	-- Add the parameters for the stored procedure here
	@StudentFirstName varchar(100),
    @StudentMiddleName varchar(100),
    @StudentLastName varchar(100),
    @StudentLocalAddress varchar(200) = NULL,
    @StudentHomeAddress varchar(200) = NULL,
    @StudentEmailAddress varchar(200),
    @StudentHomePhoneNumber varchar(50),
    @StudentMobilePhoneNumber varchar(50)
AS

DECLARE @ID1  INT
DECLARE @ID2  INT
 
BEGIN

-- Note we can also use the SCOPE_IDENTITY() function in place of @@Identity

   INSERT INTO Address
     (AddressText)
   VALUES   
     (@StudentLocalAddress)

   SELECT @ID1 = @@IDENTITY  

   INSERT INTO Address
     (AddressText)
   VALUES   
     (@StudentHomeAddress)

   SELECT @ID2 = @@IDENTITY

-- With the other two insertions we need the identity field

   INSERT INTO ContactInformation
     (PersonCurrentAddressID, PersonHomeAddressID, EmailIDText, MobileTelephoneNumberText, TelephoneNumberText)
   VALUES
      (@ID1, @ID2, @StudentEmailAddress, @StudentMobilePhoneNumber,@StudentHomePhoneNumber)

   SELECT @ID1 = @@IDENTITY

   INSERT INTO PersonName
     (PersonGivenNameText, PersonMiddleNameText, PersonSurnameText)
   VALUES
     (@StudentLastName,  @StudentMiddleName, @StudentFirstName)

   SELECT @ID2 = @@IDENTITY

   INSERT INTO Student
     (StudentNameID, StudentContactInformationID)
    VALUES
     (@ID2, @ID1 ) 

END