In the ASP.NET forums I am frequent the question of inserting information into multiple related tables is asked very often. I want to share my solution of this problem. The answer is based on this thread ASP.NET thread
Please also take a look at this article showing how to get ID of the newly inserted record.
Also, one more word about TRY/CATCH logic in this procedure. I took this idea from Kevin Goff’s article Touch/Peel/Stand, then Try/Catch/Raise
You can also review this article Retrieving the Just-Inserted ID of an IDENTITY Column Using a SqlDataSource Control dealing with the similar problem.
First, I show the whole stored procedure code. Don’t be surprised about the non-normalized data, this is what we were dealing with.
/****** Object: StoredProcedure [dbo].[PersonInsert] Script Date: 06/25/2008 15:38:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS
(
SELECT 1
FROM dbo.sysobjects
WHERE id = object_id (N'[dbo].[PersonInsert]')
AND OBJECTPROPERTY(id, N'IsProcedure') = 1
)
DROP PROCEDURE [dbo].[PersonInsert]
GO
-- =============================================
-- Author: Naomi
-- Create date: 06-27-2008
-- Description: Inserts Person's Information based on PersonType
-- =============================================
CREATE PROCEDURE [dbo].[PersonInsert]
@NewPersonID INT = NULL Output,
@PersonType CHAR(1) = 'A', -- default is Adult Volunteer
@SiteID INT,
@FirstName VARCHAR(25),
@MiddleName VARCHAR(25) = NULL,
@LastName VARCHAR(30),
@Gender CHAR(1),
@DOB DATETIME,
@UserName VARCHAR(25) = NULL,
@Pwd VARCHAR(20) = '',
@Title VARCHAR(50) = NULL,
@Address1 VARCHAR(50),
@Address2 VARCHAR(25) = NULL,
@City VARCHAR(22),
@Zip CHAR(7),
@State CHAR(2),
@Email VARCHAR(75) = NULL,
@SecondEmail VARCHAR(75) = NULL,
@ScreenName VARCHAR(25) = NULL,
@HomePhone VARCHAR(25),
@CellPhone VARCHAR(25) = NULL,
@Comment VARCHAR(MAX) = NULL,
@FComment VARCHAR(MAX) = NULL,
@MotherID INT = 0,
@FatherID INT = 0,
@Father VARCHAR(50) = '',
@Mother VARCHAR(50) = '',
@DefaultPicture VARBINARY(MAX) = NULL,
@School VARCHAR(25) = '',
@Grade CHAR(2) = '',
@MedicalConcerns VARCHAR(150) = NULL,
@DietRestrictions VARCHAR(75) = 'NONE',
@DietaryRest VARCHAR(75) = 'NONE',
@CurrentPoints INT = 0,
@TotalPoints INT = 0,
@MitzvahRewardsID INT = NULL,
@Medications VARCHAR(50) = 'NONE',
@NonActivities VARCHAR(50) = NULL,
@LTetnus smalldatetime = NULL,
@Allergies VARCHAR(75) = 'NONE',
@TylenolAllowed bit = NULL,
@AspirinAllowed bit = NULL,
@MedicalInsurance VARCHAR(30) = NULL,
@PolicyNum VARCHAR(18) = NULL,
@EmergencyContact VARCHAR(50) = NULL,
@RelationshipContact CHAR(10) = NULL,
@EmergencyContactPhone VARCHAR(25) = NULL,
@EmergencyContact1 VARCHAR(50) = NULL,
@RelationshipContact1 CHAR(10) = NULL,
@EmergencyContactPhone1 VARCHAR(25) = NULL,
@Physician VARCHAR(30) = NULL,
@PhysicianPhone VARCHAR(25) = NULL,
@Hospital VARCHAR(18) = NULL,
@ToiletTrained bit = NULL,
@Pets VARCHAR(35) = NULL,
@TherapiesDetails VARCHAR(MAX) = NULL,
@NamesAgesSiblings VARCHAR(200) = NULL,
@ChildDescription VARCHAR(200) = NULL,
@ChildsCommunicationSkills VARCHAR(100) = NULL,
@FavActivities VARCHAR(75) = NULL,
@LFavActivities VARCHAR(75) = NULL,
@GainFromFC VARCHAR(50) = NULL,
@HeardAboutFC VARCHAR(60) = NULL,
@WantFriendAtHome bit = 0,
@Interviewed bit = 0,
@PublishPictures bit = 0,
@Occupation nvarchar(50) = NULL,
@BusinessName nvarchar(75) = NULL,
@BusAdd1 nvarchar(50) = NULL,
@BusAdd2 nvarchar(25) = NULL,
@BusCity nvarchar(22) = NULL,
@BusState CHAR(2) = NULL,
@BusZip CHAR(7) = NULL,
@BusEMail VARCHAR(70) = NULL,
@Pager VARCHAR(25) = NULL,
@Fax VARCHAR(25) = NULL,
@BusinessPhone VARCHAR(25) = NULL,
@Anniversary DATETIME = NULL,
@Affiliation nvarchar(70) = NULL,
@MaritalStatus CHAR(1) = NULL,
@SpouseID INT = 0,
@SpouseFN VARCHAR(25) = '',
@SpouseLN VARCHAR(30) = '',
@IsActive bit = 1,
@HebrewBDay nvarchar(MAX) ='',
@SchoolPhone VARCHAR(25) = '',
@FCellPhone VARCHAR(25) ='',
@MCellPhone VARCHAR(25) ='',
@FAddress VARCHAR(MAX) ='',
@MAddress VARCHAR(MAX) ='',
@IsMember bit = NULL,
@StartDate smalldatetime = NULL,
@PaymentMethod VARCHAR(MAX) = NULL,
@BillQuaterly bit = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF EXISTS
(
SELECT 1
FROM dbo.People
WHERE UserName = @UserName
AND SiteID = @SiteID
)
BEGIN
RAISERROR (N'UserName %s already exists. Please choose another username.',
16, -- Severity,
1, -- State,
@UserName)
RETURN -1
END
IF EXISTS
(
SELECT 1
FROM dbo.People
WHERE FirstName = LTRIM(@FirstName)
AND LastName = LTRIM(@LastName)
AND DOB = @DOB
AND SiteID = @SiteID
)
BEGIN
RAISERROR (N'User %s %s already exists.',
16, -- Severity,
1, -- State,
@FirstName, @LastName)
RETURN -1
END
BEGIN TRY
--set @DefaultPicture = CONVERT(varbinary(max),@DefaultPicture)
BEGIN TRANSACTION
-- All types first insert into People table
INSERT
INTO People
(
IsActive ,
FirstName ,
MiddleName ,
LastName ,
Gender ,
DOB ,
Address1 ,
Address2 ,
City ,
State ,
Zip ,
Email ,
SecondEmail,
ScreenName ,
HomePhone ,
CellPhone ,
Comment ,
UserName ,
Pwd ,
Mother ,
Father ,
PersonType ,
SiteID ,
DefaultPicture
)
VALUES
(
ISNULL(@IsActive, 1) ,
LTRIM(@FirstName) ,
LTRIM(ISNULL(@MiddleName,'')) ,
LTRIM(@LastName) ,
@Gender ,
@DOB ,
@Address1 ,
@Address2 ,
@City ,
@State ,
@Zip ,
@Email ,
@SecondEmail ,
@ScreenName ,
@HomePhone ,
@CellPhone ,
@Comment ,
@UserName ,
@Pwd ,
isnull(@MotherID,0) ,
isnull(@FatherID, 0) ,
dbo.GetPersonType(@PersonType, @Gender),
@SiteID ,
@DefaultPicture)
--print 'Inserted new record into People'
SET @NewPersonID = scope_identity()
IF @PersonType IN ('O','W','B','G','E','F','V','M')
-- Teen Volunteer or Teen Bnei Mitzvah or Volunteer In Training or Friends
BEGIN
IF @PersonType IN ('O','W','B','G') -- Volunteer In Training or Friends
BEGIN
INSERT
INTO Friends
(
School ,
Grade ,
MedicalConcerns ,
Medications ,
NonActivities ,
LTetnus ,
Allergies ,
DietaryRest ,
TylenolAllowed ,
AspirinAllowed ,
MedicalInsurance ,
PolicyNum ,
EmergencyContact ,
EmergencyContactPhone ,
RelationshipContact ,
EmergencyContact1 ,
EmergencyContactPhone1 ,
RelationshipContact1 ,
Physician ,
PhysicianPhone ,
Hospital ,
ToiletTrained ,
Pets ,
TherapiesDetails ,
NamesAgesSiblings ,
ChildDescription ,
ChildsCommunicationSkills,
FavActivities ,
LFavActivities ,
GainFromFC ,
HeardAboutFC ,
WantFriendAtHome ,
Interviewed ,
Comment ,
PublishPictures ,
FriendID
)
VALUES
(
isnull(@School,'') ,
LTRIM(RTRIM( isnull(@Grade,''))) ,
isnull(@MedicalConcerns,'NONE') ,
isnull(@Medications,'NONE') ,
isnull(@NonActivities,'') ,
@LTetnus ,
isnull(@Allergies,'NONE') ,
isnull(@DietaryRest,'NONE') ,
@TylenolAllowed ,
@AspirinAllowed ,
isnull(@MedicalInsurance,'') ,
isnull(@PolicyNum,'') ,
isnull(@EmergencyContact,'') ,
isnull(@EmergencyContactPhone,'') ,
isnull(@RelationshipContact,'') ,
isnull(@EmergencyContact1,'') ,
isnull(@EmergencyContactPhone1,'') ,
isnull(@RelationshipContact1,'') ,
isnull(@Physician,'') ,
isnull(@PhysicianPhone,'') ,
isnull(@Hospital,'') ,
@ToiletTrained ,
isnull(@Pets,'') ,
isnull(@TherapiesDetails,'') ,
isnull(@NamesAgesSiblings,'') ,
isnull(@ChildDescription,'') ,
isnull(@ChildsCommunicationSkills,''),
isnull(@FavActivities,'') ,
isnull(@LFavActivities,'') ,
isnull(@GainFromFC,'') ,
isnull(@HeardAboutFC,'') ,
@WantFriendAtHome ,
@Interviewed ,
isnull(@Comment,'') ,
@PublishPictures ,
@NewPersonID
)
END
IF @PersonType IN ('O', 'W','E','F','V','M')
-- Volunteer In Training or Teen Volunteer or Teen Bnei Mitzvah
BEGIN
INSERT
INTO Volunteers
(
Grade ,
School ,
Medications ,
DietRestrictions,
Allergies ,
PersonID
)
VALUES
(
LTRIM(RTRIM(@Grade)) ,
@School ,
isnull(@Medications,'NONE'),
isnull(@DietaryRest,'NONE'),
isnull(@Allergies,'NONE') ,
@NewPersonID
)
END
END
ELSE -- Regular Person / Donors / Adult Volunteer
BEGIN
-- First we update Spouse's information with this new PersonID
IF COALESCE(@SpouseID,0) > 0 -- we're trying to set new SpouseID
UPDATE AdultInfo
SET SpouseID = @NewPersonID
WHERE PersonID =
(
SELECT SpouseID
FROM AdultInfo
WHERE PersonID = @SpouseID
AND COALESCE(SpouseID,0) = 0
)
-- What if we had Spouse pointing already to somebody else ?
IF EXISTS
(
SELECT SpouseID
FROM AdultInfo
WHERE PersonID = @SpouseID
AND COALESCE(SpouseID,0) > 0
AND SpouseID <> @NewPersonID
)
BEGIN
-- THROW an Error here
RAISERROR (N'The Spouse information is incorrect.',
16, -- Severity,
1 -- State,
)
-- Execution automatically stops here and we'll be now in the CATCH block
END
ELSE
BEGIN
INSERT
INTO AdultInfo
(
PersonID ,
Title ,
Occupation ,
BusinessName ,
BusAdd1 ,
BusAdd2 ,
BusCity ,
BusState ,
BusZip ,
Pager ,
Fax ,
BusinessPhone,
SpouseFN ,
SpouseLN ,
SpouseID ,
Anniversary ,
MaritalStatus
)
VALUES
(
@NewPersonID ,
@Title ,
@Occupation ,
@BusinessName ,
@BusAdd1 ,
@BusAdd2 ,
@BusCity ,
@BusState ,
@BusZip ,
@Pager ,
@Fax ,
@BusinessPhone,
@SpouseFN ,
@SpouseLN ,
@SpouseID ,
@Anniversary ,
@MaritalStatus
)
END
END
--All person's types processed
IF @SiteID = 1 AND COALESCE(@IsMember,0) > 0
INSERT
INTO MEMBERSHIP
(
PersonID ,
IsMember ,
StartDate ,
PaymentMethod,
BillQuaterly
)
VALUES
(
@NewPersonID ,
@IsMember ,
@StartDate ,
@PaymentMethod,
@BillQuaterly
)
COMMIT TRANSACTION
END TRY
BEGIN
CATCH
DECLARE @ErrorSeverity INT,
@ErrorNumber INT,
@ErrorMessage nvarchar(4000),
@ErrorState INT,
@ErrorLine INT,
@ErrorProc nvarchar(200)
-- Grab error information from SQL functions
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorNumber = ERROR_NUMBER()
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorState = ERROR_STATE()
SET @ErrorLine = ERROR_LINE()
SET @ErrorProc = ERROR_PROCEDURE()
SET @ErrorMessage = 'Problem updating person''s information.' + CHAR(13) + 'SQL Server Error Message is: ' + CAST(@ErrorNumber AS VARCHAR(10)) + ' in procedure: ' + @ErrorProc + ' Line: ' + CAST(@ErrorLine AS VARCHAR(10)) + ' Error text: ' + @ErrorMessage
-- Not all errors generate an error state, to set to 1 if it's zero
IF @ErrorState = 0
SET @ErrorState = 1
-- If the error renders the transaction as uncommittable or we have open transactions, we may want to rollback
IF @@TRANCOUNT > 0
BEGIN
--print 'Rollback transaction'
ROLLBACK TRANSACTION
END
RAISERROR (@ErrorMessage , @ErrorSeverity, @ErrorState, @ErrorNumber)
END CATCH
RETURN @@ERROR
END
GO
And the way to grab the ID of newly inserted person using SQLDataSource:
#region DataSource Inserted
protected void DataSource_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
if (e.Command.Parameters["@NewPersonID"].Value != DBNull.Value)
{ this.NewPersonID = Convert.ToInt32(e.Command.Parameters["@NewPersonID"].Value); }
else
{ this.NewPersonID = 0; }
}
#endregion
Please also see How to return Error Messages from the SQL Server to the application for more discussion about actual implementation of this SP in the web application.
I want to also note, that in SQL Server 2008 and up we can simplify the insert into 2 tables by using composable DML.
