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
Solved

large stored procedure versus small stored procedure

Posted on 2010-11-15
5
466 Views
Last Modified: 2012-05-10
I am trying to determine if it is better to build one large stored procedure with many parameters, or several smaller procedures with fewer parameters.  Besides the question of performance, I have a concern about being able to provide feedback to the user to know what is going on if the procedure takes a long time to run.  For example, if I wrote the code in vb.net and embedded it the program, I would keep sending messages to a status strip as each step of the process was started and/or completed.  I don't know how I can do this with a stored procedure if I had one long procedure instead of several shorter ones.

0
Comment
Question by:jsaacson
  • 3
  • 2
5 Comments
 
LVL 13

Accepted Solution

by:
gbanik earned 250 total points
ID: 34137339
If your concern is to just send status messages to the client about the progress, it can be done from a procedure irrespective of a long or multiple short ones. Your objective rather should be... Is there any reusable code that I must split into multiple procedures... can I optimize the long procedure... etc.
0
 
LVL 13

Assisted Solution

by:gbanik
gbanik earned 250 total points
ID: 34137371
0
 

Author Comment

by:jsaacson
ID: 34137501
I am concerned about returning status messages, as I do not know how to feed back a message from a stored procedure to my vb.net program and show it to the user without waiting for the stored procedure to end.  

There is reusable code, as the process will gather data from the same tables but for different periods (ie current year, prior year).

0
 
LVL 13

Expert Comment

by:gbanik
ID: 34137971
Check the last link... this is what u want. Note that the status messages are asynchronous in nature.
0
 

Author Comment

by:jsaacson
ID: 34138003
I have been looking at them and I think it is beginning to make sense to me.  I am going to play with this for a while and see how it works.

I appreciate the help you both have given.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
A short article about a problem I had getting the GPS LocationListener working.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

789 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