HI David, it something like the following. However, you may have to tweak it depending on your DR form fields and what your shortname fields are,etc. create procedure usp_DR_ASPDOTNETSTOREFRONT_InsertSQL (@UserID int,@PortalID int,@URLReferrer nvarchar(1000),@IPAddress nvarchar(20), @Username nvarchar(200),@Firstname nvarchar(50),@Lastname nvarchar(50), @Email nvarchar(200),@Howdid nvarchar(50),@Phone nvarchar(20),@OKto nvarchar(5), @FirstnameBill nvarchar(50),@LastnameBill nvarchar(50),@PhoneBill nvarchar(20), @CompanyBill nvarchar(200),@AddressTypeBill nvarchar(50),@Address1Bill nvarchar(200), @Address2Bill nvarchar(200),@SuiteBill nvarchar(100),@CityBill nvarchar(100), @CountryBill nvarchar(100),@ZipBill nvarchar(15),@StateBill nvarchar(50), @Sameas nvarchar(5), @FirstnameShip nvarchar(50),@LastnameShip nvarchar(50), @PhoneShip nvarchar(20),@CompanyShip nvarchar(200),@AddressTypeShip nvarchar(50), @Address1Ship nvarchar(200),@Address2Ship nvarchar(200),@SuiteShip nvarchar(100), @CityShip nvarchar(100),@CountryShip nvarchar(100),@ZipShip nvarchar(15),@StateShip nvarchar(50)) as begin declare @OkayToEmail int declare @BillingEqualsShipping int declare @FullnameBill nvarchar(200) declare @FullnameShip nvarchar(200) declare @BillingAddressID int declare @ShippingAddressID int declare @CustomerID int set @FullnameBill = @FirstnameBill + ' ' + @LastnameBill set @FullnameShip = @FirstnameShip + ' ' + @LastnameShip if (@OKto = 'Yes') set @OkayToEmail = 1 else set @OkayToEmail = 0 if (@Sameas = 'Yes') set @BillingEqualsShipping = 1 else set @BillingEqualsShipping = 0 -- check if customer already exists based on UserID, if not insert new record if not exists (select userid from aspdnsf_tcustomer where userid = @UserID) begin -- insert into ASPDOTNETSTOREFRONT aspdnsf_tcustomer SQL table insert into aspdnsf_tcustomer(CustomerGUID,CustomerLevelID,RegisterDate,Phone,AffiliateID,Referrer,OkToEmail, BillingEqualsShipping,LastIPAddress,LocaleSetting,RecurringShippingMethodID,BillingAddressID,ShippingAddressID, GiftRegistryGUID,GiftRegistryIsAnonymous,GiftRegistryAllowSearchByOthers,GiftRegistryHideShippingAddresses, CODCompanyCheckAllowed,CODNet30Allowed,Deleted,CreatedOn,Over13Checked,VATSetting,StoreCCInDB,IsRegistered, AdminCanViewCC,Active,UserID,PortalID) values(newid(),0,getdate(),@Phone,0,@URLReferrer,@OkayToEmail,@BillingEqualsShipping,@IPAddress,'en-US',1,@BillingAddressID,@ShippingAddressID, newid(),1,1,1,0,0,0,getdate(),0,0,1,1,1,1,@UserID,@PortalID) set @CustomerID = (select scope_identity()) -- insert into ASPDOTNETSTOREFRONT aspdnsf_address SQL table for Billing insert into aspdnsf_address(AddressGUID,CustomerID,FirstName,LastName,Company,Address1,Address2,Suite,City, State,Zip,Country,ResidenceType,Phone,Email,CardName,eCheckBankAccountName,Deleted,CreatedOn,Crypt,PortalID) values(newid(),@CustomerID,@FirstnameBill,@LastnameBill,@CompanyBill,@Address1Bill,@Address2Bill,@SuiteBill,@CityBill, @StateBill,@ZipBill,@CountryBill,@AddressTypeBill,@PhoneBill,@Email,@FullnameBill,@FullnameBill,0,getdate(),0,@PortalID) set @BillingAddressID = (select scope_identity()) -- insert into ASPDOTNETSTOREFRONT aspdnsf_address SQL table for Shipping insert into aspdnsf_address(AddressGUID,CustomerID,FirstName,LastName,Company,Address1,Address2,Suite,City, State,Zip,Country,ResidenceType,Phone,Email,CardName,eCheckBankAccountName,Deleted,CreatedOn,Crypt,PortalID) values(newid(),@CustomerID,@FirstnameShip,@LastnameShip,@CompanyShip,@Address1Ship,@Address2Ship,@SuiteShip,@CityShip, @StateShip,@ZipShip,@CountryShip,@AddressTypeShip,@PhoneShip,@Email,@FullnameShip,@FullnameShip,0,getdate(),0,@PortalID) set @ShippingAddressID = (select scope_identity()) -- now update aspdnsf_tcustomer table with the corresponding BillingAddressID / ShippingAddressID update aspdnsf_tcustomer set BillingAddressID = @BillingAddressID, ShippingAddressID = @ShippingAddressID where CustomerID = @CustomerID end -- update record instead else if exists (select userid from aspdnsf_tcustomer where userid = @UserID) begin declare @UpdateBillingAddressID int declare @UpdateShippingAddressID int declare @UpdateCustomerID int set @UpdateBillingAddressID = (select BillingAddressID from aspdnsf_tcustomer where userid = @UserID) set @UpdateShippingAddressID = (select ShippingAddressID from aspdnsf_tcustomer where userid = @UserID) set @UpdateCustomerID = (select CustomerID from aspdnsf_tcustomer where userid = @UserID) -- update tcustomer table update aspdnsf_tcustomer set Phone = @Phone, Referrer = @URLReferrer,OkToEmail = @OkayToEmail, LastIPAddress = @IPAddress, BillingEqualsShipping = @BillingEqualsShipping where userid = @UserID -- update billing record update aspdnsf_address set FirstName = @FirstnameBill, LastName = @LastnameBill,Company = @CompanyBill, Address1 = @Address1Bill, Address2 = @Address2Bill, Suite = @SuiteBill, City = @CityBill, State = @StateBill, Zip = @ZipBill, Country = @CountryBill, ResidenceType = @AddressTypeBill, Phone = @PhoneBill, Email = @Email, CardName = @FullnameBill, eCheckBankAccountName = @FullnameBill, PortalID= @PortalID where AddressID = @UpdateBillingAddressID and CustomerID = @UpdateCustomerID -- update shipping record update aspdnsf_address set FirstName = @FirstnameShip, LastName = @LastnameShip,Company = @CompanyShip, Address1 = @Address1Ship, Address2 = @Address2Ship, Suite = @SuiteShip, City = @CityShip, State = @StateShip, Zip = @ZipShip, Country = @CountryShip, ResidenceType = @AddressTypeShip, Phone = @PhoneShip, Email = @Email, CardName = @FullnameShip, eCheckBankAccountName = @FullnameShip, PortalID= @PortalID where AddressID = @UpdateShippingAddressID and CustomerID = @UpdateCustomerID end end -- David |