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.
| Author |
Topic |
|
DougFord99
Starting Member
10 Posts |
 Posted - 2000-12-29 : 19:11:06
|
| I am passing HTML form data to an SProc via ASP to create a new customer. Customer data is stored in over 10 different tables to conform to fourth form normalization rules. On this particular HTML form, the user can add up to 3 different phone numbers to a customer. Each phone number can be one of many different types (cellular, fax, day phone, evening phone, etc.) selected from a dynamically generated combo box within the form.Customers are stored in a customer table, phone numbers are stored in a phone table and phone types are stored in a phonetype table. The phonenumber and the phonenumbertypeid are being passed from ASP using:conn.execute("spCreateCustomer "&request.form("phone1")&","&request.form("phonetypeid1")&",etc...")into the following variables in a SProc:CREATE PROCEDURE spCreateCustomer@phone1 varchar(10)@phonetypeid1 int@phone2 varchar(10)@phonetypeid2 int@phone3 varchar(10)@phonetypeid3 int...I have created the customer and retrieved the customerid using @@IDENTITY. Now I want to create a new phone record for each phone number passed through the form. As of right now I have created a unique insert statement for each phone number/type like this:// Create first new phone record for general contactINSERT INTO phone (customerid,phonetypeid,phonenumber) VALUES (@customerid,@phonetypeid1,@phone1)// Create second new phone record for general contactINSERT INTO phone (customerid,phonetypeid,phonenumber) VALUES (@customerid,@phonetypeid2,@phone2) // Create third new phone record for general contactINSERT INTO phone (customerid,phonetypeid,phonenumber) VALUES (@customerid,@phonetypeid3,@phone3)The only thing different in each statement is the number following the variable (1,2,3). It seems like there would have to be an easier, cleaner way of doing this without repeating the code over three times in a row?Thanks for your help...Doug FordDoug FordMCSE,MCT |
|
|
|
|
|
|
|
Looping in a SProc