473,700 Members | 2,792 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 1454
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
5434
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
16943
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
17352
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
5270
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
7647
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
2996
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
2753
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
14956
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
2114
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
8709
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
8638
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9202
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
9058
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
8909
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
5894
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();...
1
3081
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
2
2371
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2018
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.