insert into multile tables using stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

insert into multile tables using stored procedure

Author

Topic

mgriffiths
Starting Member

1 Post

Posted - 2014-10-06 : 06:09:04

I am trying to create a stored procedure that will insert data into the customers table, which is the parent table, then take the custid from the inserted row to use to insert data into another table

heres what I have so far but it not going too well.

CREATE PROCEDURE CustomerDetails.bnc_insNewRegistration
-- Add the parameters for the stored procedure here
@CustId int,
@CompanyName varchar(100),
@FirstName varchar(50),
@LastName varchar(50),
@Email nvarchar(254),
@HouseStreet varchar(100),
@Town smallint,
@County tinyint,
@Postcode char(8),
@Password nvarchar(20)

AS
BEGIN

begin tran

insert into CustomerDetails.Customers
(CompanyName, FirstName, LastName, EmailAddress)
Values (@CompanyName, @FirstName, @LastName, @Email)

set @CustId = (select CustId from inserted)

insert into CustomerDetails.Address
(CustomerId, HouseNoAndStreet, Town, County, PostCode)
values (@CustId, @HouseStreet, @Town, @County, @Postcode)

insert into CustomerDetails.MembershipDetails
(CustomerId, UserName, Password)
values (@CustId, @Email, @Password)

commit tran

END
GO

I am fairly new to all of this so please forgive me if any of this sounds ridiculous at all. It is for a registration for on an asp.net web site, which will require inserting data into 3 tables at once using the generated custid from the customers table.

Thank you very much in advance and I wont be offended at all if I am told im being stupid lol

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2014-10-06 : 06:46:10

Please see below:

begin tran

begin try

insert into CustomerDetails.Customers
(CompanyName, FirstName, LastName, EmailAddress)
Values (@CompanyName, @FirstName, @LastName, @Email)

select @CustId = SCOPE_IDENTITY()

insert into CustomerDetails.Address
(CustomerId, HouseNoAndStreet, Town, County, PostCode)
values (@CustId, @HouseStreet, @Town, @County, @Postcode)

insert into CustomerDetails.MembershipDetails
(CustomerId, UserName, Password)
values (@CustId, @Email, @Password)
end try

begin catch
 SELECT ERROR_MESSAGE() AS ErrorMessage
 if @@TRANCOUNT > 0
        rollback tran
end catch

if @@TRANCOUNT > 0
  commit tran

Harsh Athalye
http://in.linkedin.com/in/harshathalye/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources