Solved

large stored procedure versus small stored procedure

Posted on 2010-11-15
5
467 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
[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
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…

730 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