473,602 Members | 2,787 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 1452
SQLnewbie (ga*******@hotm ail.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****@sommarsk og.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*******@hotm ail.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****@sommarsk og.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
5427
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 this error message: "The Error Message was: Error detected by database DLL.from: Crystal Reports ActiveX Designer". What am I doing wrong in the SubReport section below and how do I properly pass in sproc params to the subreport? Thanks. ...
3
16937
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 create a parameter query in a stored procedure, but how do I use the result set of a parameter query in a select query (in the same or another sp)? In short, if a select query contains a result table that is generated as a parameter query, how do I...
2
17348
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 (update, append) should be activated in order to transfer data to other tables. I tried to avoid any coding in VB, as I am not a professional, but I have found a statement in an article, that, unlike select queries, form's Input Property can't be...
2
5266
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 clicks once they have selected the desired record. The button calls a report which uses a stored procedure as its record source. The SP has 2 input parameters, one of which is a datetime data type. In the input parameters data field of the...
39
7629
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 indicate: a) That I don't know enough b) Passing arguments by ref is bad
4
2983
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 pass it to the Db and retrieveing data from a stored procedure, but I can't get the hang of parameters. I have a method where I can get the parameters passed to the sp but it doesn't want to return any results. Here's a copy of my code:
4
2750
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 to a stored procedure on the details2.aspx page. I can successfully pass the values from the listbox control to a
1
14954
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 int ) AS
1
2108
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 if any records are returned. I will later insert some conditional statements. Here is my Default.aspx.cs protected void btnNext_Click(object sender, EventArgs e) {
0
7993
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8405
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8404
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8270
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6730
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5440
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3901
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3944
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2424
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.