Stored Procedure insert 2 tables w/ relationship of 1 to many

T

ToniS

I have a stored procedure that I am trying to insert to two tables,
Exhibitors and ExhibitorsAddress. Each Exhibitor will have at least one
record with an AddressType of 1. An Exhibitor may not have an addresstype
of 2 or they may have several addressType of 2. Is there a way to have a
'Public ' type
variable so when a record with an addresstype of 2 I can use the ExhID that
was used to create the Exhibitor when it had an Address type 1.

Ex data,
Exh1 Adresstype=1
Exh1 Addresstype=2
Exh2 Addresstype=1
Exh3 AddressType=1
Exh3 AddressType=2
Exh3 AddressType=2
Exh3 AddressType=2
Exh4 AddressType=1

This is what I have so far for my Stored Procedure.....

ALTER Procedure StoredProcConvertExh (
@ExhibitorShortName varchar(15),
@ExhibitorName varchar(25),
@ProductDescription varchar(100),
@DivisionID uniqueidentifier,
@ShowCategories tinyint,
@NewExhibitor bit,
@AddressType tinyint,
@FirstName varchar(15),
@LastName varchar(20),
@Address1 varchar(30),
@Address2 varchar(30),
@City varchar(30),
@State char(2),
@ZipCode varchar(10),
@phone varChar(12),
@Fax varchar(12),
@PacketSent bit
)
As
set nocount on

Declare @InsertErr int
Declare @ExhibitorID uniqueidentifier
Declare @ExhibitorAddressID uniqueidentifier
Declare Pubilc @PubExhID as uniqueidentifier ' not valid

Begin Transaction
if @Addresstype = 1
begin
Set @ExhibitorID = NEWID()
pubexhid = @ExhibitorID

Insert Into Exhibitors (ExhibitorID, ExhibitorShortName, ExhibitorName,
ProductDescription, DivisionID, ShowCategories, NewExhibitor)
Values (@ExhibitorID, @ExhibitorShortName, @ExhibitorName,
@ProductDescription, @DivisionID, @ShowCategories, @NewExhibitor)
end
else
begin
set @exhibitorID = PubExhID
end

Set @InsertErr = @@Error

If @InsertErr = 0
Begin
Set @ExhibitorAddressID = NewID()

Insert Into ExhibitorAddresses (ExhibitorAddressID, ExhibitorID, FirstName,
LastName, Address1, Address2, City, State, Zipcode, Phone, Fax, PacketSent,
AddressType)
Values (@ExhibitorAddressID, @ExhibitorID, @FirstName, @LastName,
@Address1, @Address2, @City, @State, @ZipCode, @Phone, @Fax, @PacketSent,
@AddressType)

Set @InsertErr = @@Error

End

If @InsertErr = 0
Begin
Commit Transaction
Return 0
End
Else
Begin
RaisError('Convert Exhbitor and Exhibitor Address Table error, transaction
rolled back.', 16, 1)
Rollback Transaction
Return 1
End


Any suggestions would be greatly appreciated
Tonis
 
Top