473,320 Members | 2,112 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Passing control flags to stored procedure

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
4 1440
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Aaron | last post by:
The following code works fine when previewing a Crystal report using ASP, EXCEPT when it gets to a report using a SubReport and its associated parameters. The whole report just comes up blank with...
3
by: WGW | last post by:
Though I am a novice to MS SQL server (2000 I believe), I can do almost! everything I need. Maybe not efficiently, but usefully. However, I have a problem -- a complex query problem... I can...
2
by: zlatko | last post by:
There is a form in an Access Project (.adp, Access front end with SQL Server) for entering data into a table for temporary storing. Then, by clicking a botton, several action stored procedures...
2
by: Bob | last post by:
I'm new to Access projects and SQL server and am not a veteran VB programmer. There's a cry for help! I'm attempting to print the current form on screen by using a command button which the user...
39
by: Mike MacSween | last post by:
Just spent a happy 10 mins trying to understand a function I wrote sometime ago. Then remembered that arguments are passed by reference, by default. Does the fact that this slowed me down...
4
by: Mike Dinnis | last post by:
Hi, I've been working through a number of turorials to try to learn more about retrieving data from a SQL database. I think i've mastered techniques where i create a sql string in the page and...
4
by: Ranginald | last post by:
Hi, I'm having trouble passing a parameter from my default.aspx page to my default2.aspx page. I have values from a query in a list box and the goal is to pass the "catID" from default.aspx...
1
by: chariclark | last post by:
This may be a quick fix post... ---------------------------- I am having trouble passing multiple values into stored procedure. Here it is below: CREATE Procedure spGetAssociateds ( @PDSI...
1
by: vncntj | last post by:
I have a C#.NET that simply passes 6 values to a Stored Procedure. But I'm trying to get the (Default.aspx.cs page) to handle passing the values to the sp. The goal is to pass the values and see...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.