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 |
|
tuenty
Constraint Violating Yak Guru
278 Posts |
 Posted - 2004-10-29 : 18:09:17
|
I want to create a SP that creates contracts. companyDivision<Contract<life<>studentHere are some business rules I will implement in this stored procedure=====================================================Each contract may have one or more persons. Each contract will update from three to six tables.All contracts must have a main subscriber. If the main subscriber marital status is married and the contract is a Couple or Family contract then the contract may have one active spouse (there might be other spouses but not active). If the contract is a Dependent contract it may have onlyone dependent if it is a Family contract it may have one or more dependents. Some of the dependents might be studentsEven when a user is allowed to update a table it may not do certain kind of changes although others can, some rows can be altered by a user others not. This information is stored in tables and there will be a SP to determine if the user is allowed to perform the transaction or notWhat do you think are these parameters ok for my goal??@user nvarChar(10),--will be use after determining the action to be performed to verify if the user can execute the transaction@subscID char(9),--will be used to determine if the subscriber was previously in the DATABASE and since it is the same for spouse and dependents it will not have to be send for each life in the contract@subscriberData nvarchar(?),--will have contact info, contract definition, and subscriber some other data (name, dob, etc) *one record@spouseData nvarchar(?),--spouse info*it might have 0, 1, or 2 records(if canceling previous spouse and adding a new one)@dependentData nvarchar(?),--dependent info with student certification if applies* from 0 to what ever (the biggest set has been 12, I think)@rowDelimiter char(1),--the delimiter for the fields in @spouseData, @dependentData@fieldDelimiter char(1) --the delimiter for the fields in @subscriberData, @spouseData, @dependentDataI will be updating data intblContact-PK subscID ( last contact information for each subscriber)tblContract-PK subscID and contractSeq (contract definition)tblContractLife-PK subscID,contractSeq, and lifeID (life info including subscriber, spouse, and dependents)tblStudent- PK subscID,contractSeq, and lifeID (optional student cert info)tblAlternateID-PK company, altID, subscID, dob, twinCode (optional cross reference table might be used per life, per contract)tblExtra-PK subscID, contractSeq, dob, twinCode (Optional extra information for one specific kind of contract per life)I know this is not a great design but I can not change the DB structureSo, do you think are these parameters ok???  *.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*A candle loses nothing by lighting another candle |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-29 : 18:46:18
|
| Where we have to transfer a bundle of data to an SProc (which is rare, but happens) we are tending to use XML now. Particularly where the size of the data is moving into the TEXT, rather than VARCHAR, size bracket.But its horses-for-courses, so it depends on the problem you actually have to solve.Kristen |
 |
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2004-11-01 : 11:17:50
|
| Thanx KristenMore info:There will be 3 internal applications (one for data entries and two for batch processes) and one on the Internet for the clients. (Hopefully I will eliminate one of the batch processes later on)@subscriberData will have from 202 to 305 characters@spouseData will have from 57 to 134 characters (times 0, 1, or 2)@dependentData will have from 66 to 142 (times 0, 1, ..., 15 but usually not more than 3)===================================="...Xml does not optimize for searching for a node in a document. Nodes can be arbitrarily ordered inthe document tree. Nodes cannot be indexed, although they can be sorted. Additionally, your code has to suffer the initial hit of loading the document over from the database, which itself takes time, especially if the database lives far away on the network.""...If your component lives not only on a different machine from your databases, but on an entirely different operating system, then XML is the perfect choice for delivering your data..." If not you should use data directly from the database or use an ADO disconnected recordsetThe information was taken from the book Effective Visual Basic-How to improve your vb/com+ applications Do you still the XML be more efficient even when working within the LAN? Do you have some pro and cons of using XML??I think the code will be more readable and structured though it might be more tedious, since I have no experience working with XML and I will have to convince 2 other programmers of the benefits of using XML, but in the other hand it doesn't seem to be too complicatedI found this link http://msdn.microsoft.com/library/en-us/dnsqlpro01/html/sql01c5.asp?frame=trueCan you give me some other links??*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*A candle loses nothing by lighting another candle |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2004-11-01 : 15:07:43
|
| I think what Kristen is talking about when using XML in a stored procedure is to use the OPENXML command to treat the xml document like a table. This way you could insert, update, and delete a boat load of information in a single stored procedure call. Dustin Michaels |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-01 : 16:34:48
|
| Perzactly DustinMichaelstuenty has what looks to be less that 8,000 bytes of data, so, I would guess, my XML approach is less appropriateKristen |
|
|
|
|
|
|
|
|
do you think are these parameters ok?