| Author |
Topic |
|
natas
Yak Posting Veteran
51 Posts |
 Posted - 2005-10-25 : 11:53:11
|
Hello.  I want to INSERT 1 row into 1 parent table, and take this IDENTITY and use it to insert 5-10 rows in a relational child table, the number of child INSERT:s is unkown at compile-time. So it can differ from 5-10 INSERT:s.So, i want to do a SPROC that do this in a sweet transaction, but since SQL Server doesnt have arrays, how can i solve this problemo?..  |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-10-25 : 13:20:59
|
| You can use a temp table (create the temp table, populate it then call the SP to access it) or pass a csv string to the sp - maybe a csv string for each column in the child table?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
natas
Yak Posting Veteran
51 Posts |
Posted - 2005-10-25 : 13:59:48
|
| Thanks for your answer but how do i pass the array och columns to the child table via a temp table? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-25 : 14:01:59
|
| 1) Use SProc to insert the parent, and return its IDENTITY to the application so that it can insert the children with the same ID2) Call Sproc with Parent details and Comma-delimited or XML data for the children. SProc inserts the Parent and then either SPLITS the Comma-delimited data to a table and inserts the Children from there, or uses the XML in a similar fashion.Messy!3) The SProc has enough parameters for up to 10 children, and after creating Parent inserts Child Rows [using the numerous Parameters to the SProc] until it finds NULL parameters (or have a Parameter to indicate how many children there are)Kristen |
|
|
|
natas
Yak Posting Veteran
51 Posts |
Posted - 2005-10-26 : 05:59:43
|
1. If i do that i cant include every T-SQL i need into 1 SPROC. And i also has to execute some dynamic SQL. I want to do everything in 1 SPROC and in 1 transaction within the SPROC.2. If i do it with a csv the children columns cannot contain "," . The children has to be able to contain all sorts of characters. 3. The child table must be able to handle infinite number of child rows, the 5-10 was just to explain that it is dynamic.So that leaves the option to the XML alternative i guess. But i think that parsing XML would add some good overhead, not making the code effective.So that leaves me to the question: Why doesnt the SQL Server team add arrays to SQL Server?. To do transactional parent-child INSERT:s cannot be done effectivily without arrays.. This is strange  |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-10-26 : 08:17:44
|
| "1) Use SProc to insert the parent, and return its IDENTITY to the application so that it can insert the children with the same ID""1. If i do that i cant include every T-SQL i need into 1 SPROC. And i also has to execute some dynamic SQL. I want to do everything in 1 SPROC and in 1 transaction within the SPROC."Then perhaps you might want to rethink your design. It is not always best to do everything in 1 Stored Proc.Help us help YOU!Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx*need more coffee*SELECT * FROM Users WHERE CLUE > 0(0 row(s) affected) |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2005-10-26 : 12:08:30
|
I have a similar solution. For our online shopping cart billing system.code:declare @identity intselect @identity = scope_identity() Put the above code in your stored proceedure.basically, there are 3ways to access it. scope_identity(), identity and .... boy i cant remember the 3rd do a search on BOL.however the scope_identity works best, because it works within the scope of the transcationhope this helpsafrika |
|
|
|
natas
Yak Posting Veteran
51 Posts |
Posted - 2005-10-26 : 14:57:18
|
| Then perhaps you might want to rethink your design. It is not always best to do everything in 1 Stored Proc.Don. Can you give me a detailed explaination why u think it is a better idea to:1. Execute 1 SPROC from the application that INSERT:s 1 row.2. Return the IDENTITY ID aaaaaaall the way up to the application, making the transaction lock longer than it need to be.3. Use this IDENTTITY to execute some more dynamic SQL from the application again to INSERT the children and using this IDENTITY as a foreign key.4. All of this in 1 transaction lock.I think the best solution is to send EVERYTHING that is going to get INSERT:ed into the database at once in 1 SPROC call. That would insure the transaction lock to be as short as possible and it is also safer because the IDENTITY doesnt have to travel anywhere, it stays within the SPROC. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-27 : 01:44:06
|
"2. If i do it with a csv the children columns cannot contain "," . The children has to be able to contain all sorts of characters."You can use any delimiter you like (and even "escape" the delimiter if it occurs in the data, if you need to)But its still messy!We add the lines to the order_item table as the user types them in ... rather than all at once, at the end. Dunno if that helps?  Kristen |
|
|
|
|
SP and arrays?