By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,837 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Passing control flags to stored procedure

P: n/a
Wanted to know which among these options is better and why? Or if their
could be scenarios where we could opt for one of these.

a) flags passed from code to control the execution of queries within a
stored procedure i.e. - where queries within a single stored procedure
are controlled by flags passed to them.
OR

b) Break individual queries into separate stored procedure

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
SQLnewbie (ga*******@hotmail.com) writes:
Wanted to know which among these options is better and why? Or if their
could be scenarios where we could opt for one of these.

a) flags passed from code to control the execution of queries within a
stored procedure i.e. - where queries within a single stored procedure
are controlled by flags passed to them.
OR

b) Break individual queries into separate stored procedure


Both can be useful depending on context, requirements and circumstance.
And not the least it would be a matter of taste.

See a stored procedure that encapulates something. Say that in our
system that would be a need to list the positions for an account on
a certain date. You would expect a procedure that as parameter takes
@accno and @date. Now, since we have one table for current positions,
and table with historic data, it will have to be two different queries.
But that's not really something the application should have to
bother about, and thus it should be one procedure (which possibly
forks off into subprocedures).
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2

P: n/a
But aren't their situations where controlling the stored procedures
would cause query recompilation (Execution Plan recreation) which could
lead to performance issues.

Jul 23 '05 #3

P: n/a
SQLnewbie (ga*******@hotmail.com) writes:
But aren't their situations where controlling the stored procedures
would cause query recompilation (Execution Plan recreation) which could
lead to performance issues.


Not sure what you mean here, but generally passing variables to stored
procedures does not cause recompilation.

On the other hand sometimes you may wish that it would. We had a case
in our application. There was a stored procedure that I frequenly caught
doing a complex parallel plan requiring a couple of seconds to run,
when there was a much more effcient plan for the task.

The problem was that the client that called this procedure first called
it with a 0 for its only parameter which means "bring me all from the
last 20 days". On successive calls, the client only wanted the rows
with an id higher than the highest id in the previous call. The problem
was that the plan for getting the rows for 20 days was different from
getting the delta, but it was this plan that ended up in the cache. My
solution in this case was to write to sub-procedures, one each for the
two cases. But to the client, it still looked the same. (Except that
performance was better!)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

P: n/a
Have you ever had a course in basic software engineering? Look up
coupling and cohesion.

Cohesion:
This is how well a module does one and only one thing; that it is
logically coherent. The modules should have strong cohesion. You
ought to name the module in the format "<verb><object>", where the
"<object>" is a specific logical unit in the data model. There are
several types of cohesion. We rank them going from the worst form of
cohesion to the best

1) Coincidental
2) Logical
3) Temporal
4) Procedural
5) Communicational
6) Informational
7) Functional

Coupling:
If modules have to be used in a certain order, then they are strongly
coupled. If they can be executed independently of each other and put
together like Lego blocks, then they are loosely or weakly coupled.
There are several kinds of coupling, which are ranked from worse to
best:

1) Content
2) Common
3) Control
4) Stamp
5) Data

This is covered briefly in a chapter on writing stored procedures in my
book on SQL Programming Style, which will be out later in 2005. In the
meantime, you can read DeMarco, Yourdon, Constantine, Myers or several
other of the pioneers.
This is FAR more basic than SQL programming.

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.