Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Stored procedure to write data from front end screen with master child relationship with variable number of records.

Posted on 2012-03-25
9
Medium Priority
?
444 Views
Last Modified: 2012-06-27
Hi,

I have a situation where I have to write an insert stored procedure. In this situation one client can have multiple entities and each entity can have multiple sections and each section can have three visibility levels (say 0,1 or 2); Now all these tables are linked with master child relationship. Now from the front end what should be the format to pass this information to stored procedure and how stored procedure should write these values to the tables?

Should I build a big string at the front end and then pass it to stored procedure where I should parse it into individual records and then insert into tables starting from top level master table to last child table!!!!


Can you please suggest possible solutions to this problem? Your comments and suggestions are appreciated.

Thanks.
0
Comment
Question by:ezkhan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 37763324
The client side should rather start a transaction and save into the three or four tables. You pull the tables (empty or with the clients you want to modfiy) into a dataset and have the tables at hand to work on them and then finally commit data changes in a transaction.

Bye, Olaf.
0
 

Author Comment

by:ezkhan
ID: 37763443
but, first part of my question is about the format of the data passing to the stored procedure from front end. Should it be a concatenated string where delimiters are used to seperate parent and child records or some other format?
And then in the stored procedure there would be one parameter to receive the concatenated string and then parse it into individual records and save them to table(master/child) after cast/convert operation.

Or there is some other appropriate method to do it?

Please let me know if need to explain it in further detail. Thanks.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37763806
Should it be a concatenated string where delimiters are used to seperate parent and child records or some other format?
I would not do it that way.

Or there is some other appropriate method to do it?
I would do it using a single Table Valued or an Xml parameter and save yourself the trouble of parsing the string.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 37771030
I took the time to reread, and since you're not specifying anything about the client the answer of a dataset might not help you very much, if you don't plan to use ASP.NET

But even in PHP or Java Sevlets I would most probably not use one parameter for this and just one stored proc.

Data retrieval is best done with a mapping best fitting your client side needs, a .net dataset could pull over the database structure 1:1 to the script side, so you act on table objects representing the tables and you don't serialise and deserialise anything.

If you're used to work with (de)serialisation of XML or JSON or other prorietary formats, you can do that, but deserialisation is not naturally done on the SQL Server side. This is something I would expect on your application layer, eg in a component using Ajax with an XML structure or Json towards the client and working towards sql server with several calls to either an Entity or several stored procs or simply SQLs (INSERT/UPDAT/DELETE), depends on what language you're about to use besides SQL Server.

You're talking of the need to store several things as one input, that input first goes to a webserver script and only secondary goes into SQL Server, so the composition and decomposition of data is a job for the webserver layer or in general terms th business logic in between client side GUI and SQL Server side data storage.

Bye, Olaf.
0
 

Author Comment

by:ezkhan
ID: 37792926
Hi,

Thanks Olaf for taking time and writing a detailed reply. Sorry, I could not get the complete idea right. Let me explain the requirement in bit more detail.

Data on the front end is organized similar to details given below;

CustomerName

Section1

Sub Section1
SubSection1Description
SubSection1Status      
SubSection1VisibileTo       Group1,Group2,Group5


Section2

Sub Section2
SubSection2Description
SubSection2Status      
SubSection2VisibileTo       Group3,Group4,Group5

.
.
.
.

Section10

Sub Section10
SubSection10Description
SubSection10Status      
SubSection10VisibileTo       Group1,Group2,Group3,Group4,Group5


CustomerSpecificSubSection10.1
CustomerSpecificSubSection10.1_Description
CustomerSpecificSubSection10.1Status
CustomerSpecificSubSectin10.1_VisibleTo     Group4,Group5
ClientSpecificField1
.
.
.
.
ClientSpecificField_N




CustomerSpecificSubSection10.N
CustomerSpecificSubSection10.N_Description
CustomerSpecificSubSection10.NStatus
CustomerSpecificSubSectin10.N_VisibleTo     Group4,Group5
ClientSpecificField1
.
.
.
.
ClientSpecificField_N



This is the kind of front end structure is and I need to define the process how front end should pass data values to my stored procedure where I store them in their related tables.
As if I take individual parameters for all data values then there would be unlimited set of prameters for stored procedure to process that is not feasible.

As number of parameters are variable I thought I should receive concatenated string for all the information from front end to store in database. Then I should write a routine in stored procedure to breakdown the string into records. Then wirte these records into temp tables and from there write them into there actual tables considering the Primary Key and Foreign Key relationship of data tables.


Please advise if it is not the appropriate approach to complete above requirement. Or if i need to explain something in more details. Your valuable comments and suggestions are appreciated.

Thanks.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37793441
Please advise if it is not the appropriate approach to complete above requirement.
It depends. It should be fine for some small application, but for anything else it is clearly not scalable.  Using T-SQL to breakdown the fields is a non-starter in anything beyond simple.  As I stated before you need to use table-valued parameters if you are using .NET or failing that use Xml.  If you want to pursue this with any of these two approaches I can go into more detail.
0
 

Author Comment

by:ezkhan
ID: 37793470
Thanks acperkins your suggestion is valid. Please advise regarding the implementation of this requirement using table-valued parameters and/or XML.

Thanks.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 37793802
Start here from SQL Server's BOL:
Table-Valued Parameters (Database Engine)
http://msdn.microsoft.com/en-us/library/bb510489(v=sql.105).aspx

And then check out these two blogs on the subject:
SQL SERVER – 2008 – Introduction to Table-Valued Parameters with Example
http://blog.sqlauthority.com/2008/08/31/sql-server-table-valued-parameters-in-sql-server-2008/
Passing table valued parameters in SQL Server 2008
http://www.techrepublic.com/blog/datacenter/passing-table-valued-parameters-in-sql-server-2008/168
0
 

Author Closing Comment

by:ezkhan
ID: 37845184
Brilliant Answer. Thanks.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question