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
426 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 29

Expert Comment

by:Olaf Doschke
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:ezkhan
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
Brilliant Answer. Thanks.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

771 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now