Solved

large stored procedure versus small stored procedure

Posted on 2010-11-15
5
468 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

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…
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Six Sigma Control Plans

689 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