Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

large stored procedure versus small stored procedure

Posted on 2010-11-15
5
Medium Priority
?
471 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 1000 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 1000 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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 connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Starting up a Project
Suggested Courses

636 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