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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-14 : 11:58:25
|
Doug writes "I am writing a data entry application using ASP. I have collected pieces of customer data from several forms and presented the data back to the data entry user for approval. Once approved I need to add the customer data to several different tables in a normalized database AND return the customerid back to ASP.
This piece of the application is going to create a private party customer (as opposed to a business customer). We treat private party customers the same as businesses in the database, but I hide the intricacies of the database from the data entry user by automatically creating particular business departments and business contacts for the private party.
Here is part of the database logic that will be helpful in answering this question:
Each customer must be labeled as either a private party or a business. This rule helps identify how the customer will be addressed. Private parties will be addressed using customer firstname lastname while businesses will be addressed by their business name. Both types of data are stored in the same customer table.
Each customer can have one or more departments.
This rule provides us with the scalabil;ty to add several departments to a single company like billing, sales, customer service, parts, etc. All customers are programatically forced to have a billing and advertising department.
Each department can have one or more addresses or department contacts. Addresses are associated with departments, not customers because some of our customers have several offices, each supporting one or more different departments. For example, a car dealer may have their sales lot at one address and their body shop at another.
Each department can be of one type (accounting, sales, service).
Each department contact can have one or more phone numbers, email address. The billing department is programmatically forced to have an accounts payable contact and the advertising department is programatically forced to have an advertising contact.
Each department contact can be one or more types (billing contact, sales contact, management contact, advertising contact,etc.)
So... what I need to do is:
1) Create a customer record and use the customerid to
2) Create a department record, then use the deptid to
3) Create a department contact, then use the deptcontactid to
4) Create an email address, then use the deptcontactis to
5) Create a phone number, then use the phoneid to
6) Link the phone to a phonetype (mobile, fax, day phone, etc.)
Finally, at the very end I need to return the customerid to ASP so I can create an advertisement for the newly created customer in the next ASP page.
This is probably TMI (too much information) for my problem, but I wanted to be as thorough as I could. Is there a way to accomplish all of this in just one SP? (I have already accomplished it using multiple SPs)
Thanks for your time,
Doug" |
|
|
|
|
|
|
|
Returning an @@IDENTITY value from a stored proc