473,881 Members | 1,755 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

recursive call in stored proc

Hi all

I am implementing a stored procedure which needs to recursively call
itself until specific condition is reached, Could anyone give some
advice about that?

Thanks a lot

Robert Song
Jul 20 '05 #1
3 6167
"Robert Song" <ro*********@md a-software.com> wrote in message
news:fb******** *************** ***@posting.goo gle.com...
Hi all

I am implementing a stored procedure which needs to recursively call
itself until specific condition is reached, Could anyone give some
advice about that?

Thanks a lot

Robert Song


One caveat is that the limit to the number of recursive calls to a procedure
is 32, that is, the count of the initial call and the subsequent recursive calls
must not be greater than 32. If you can't guarantee respecting this upper bound,
iteration might be the way to go. Even if you can, iteration might still be more
efficient than recursion. However, if you can guarantee not exceeding the
magic number 32 and the procedure is more clear recursively than
iteratively, then I'd go that way.

--
JAG
Jul 20 '05 #2
Hi John,

Thank you John, I'v got it.

Any way the reason I post this issue is: previously when i debugged
the SP of recursive call in Sql Query Analyzer I used the F10(step
over) key and it did't work. Today after read your respone, i feel
confident to debug it again. I used the key of F11(Step in) , It
works!

Thank you.

Robert Song
Jul 20 '05 #3
>> I am implementing a stored procedure which needs to recursively
call itself until specific condition is reached, Could anyone give
some advice about that? <<

Not without seeing the code first :) This request is so vague, I am
tempted to reply with "Fire good! Poison bad!"

Generally speaking, T-SQL is not a good programming language, so you
should never write anything complicated in it. Are you using
recursion because the table design is wrong? I am always scared when
someone asks for how to implement a solution they have already decided
on; the right way to ask for help is to state the problem first.
Example: "what is the best kind rock for smashing screws into wood?"
The question excludes "use a screwdriver" and seeks the simple answer
"Granite!"
Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
3250
by: Jean-Marc Blaise | last post by:
Dear all, I have simulated the windows MULTI application with a java program calling the SQLTP1DL proc referenced as DB2DARI application, on Linux Intel or ZLinux. If the proc is NOT FENCED, there is no pb and the program works fine. If either the proc is FENCED, or FENCED THREADSAFE, I get a SQL1042C, or the instance crashes on ZLinux (V8.1 FP4). The proc does a return(SQLZ_HOLD_PROC): $ /opt/IBMJava2-131/bin/java sqltp1ri 1
0
7152
by: Dave Sisk | last post by:
I've created a system or external trigger on an AS/400 file a.k.a DB2 table. (Note this is an external trigger defined with the ADDPFTRG CL command, not a SQL trigger defined with the CREATE TRIGGER statement.) I've also defined a SQL stored proc, and the trigger is set to call this SP. I've posted the simplified source below. I can manually call the stored proc, and the external trigger is created without any errors. However, when I do...
3
2288
by: Mo | last post by:
Hi, I have a webform which has vb.net code behind it and I would like it to submit the entries in the fields into a sql server db using a stored procedure. I have a central .vb file in my application that has all the stored proc calls in it. in the Submit_Click Sub in my form.vb file, I reference the function in my central .vb file which calls the stored proc. The problem that I am having is that when I submit my form, it just...
14
1847
by: Roy | last post by:
Apologies for the cross-post, but this truly is a two-sided question. Given the option of creating Looping statements within a stored proc of sql server or in the code-behind of an .net webpage, which would you choose and why? Reason I ask is I created a webpage which essentially runs through a litany of loops to determine which stored proc to kick off. This is written in the code-behind. It occurred to me that I could probably just...
0
1138
by: rsstonejr | last post by:
Hello. Has anybody ever called a stored proc on a SQL Server 2005 database from a SQL Server 2000 database? Is it even possible? I'm using the following command: sql2005server.sql2005database.dbo.sp_executesql sp_storedproc from Query Analyzer on 2000 and I'm getting the following error
3
15820
by: mandible | last post by:
I'm trying to call one stored procedure inside another. I was wondering if this is possible Some ideas I was toying with is putting the first stored procedure inside of a temp table but haven't been able to get this idea to work.
3
3199
by: oregondba | last post by:
I have a database I have inherited (new job). I am trying to baseline the code and have done a DB2Look to get the code out of an existing db. The DB2Look produced a SP with the following form: Create SP1 (various code) call sp1()
0
1571
by: skchonghk | last post by:
As title. Is it possible? Thanks. Adrian
2
5712
by: =?Utf-8?B?Vmlua2k=?= | last post by:
Hello Everyone, I can successfully insert and update the oracle database by calling a oracles stored proc from my .net code. This oracle stored proc is returning some value. I cannot see that value in my .net code Below is my .net code OracleParameter mbrid = new OracleParameter("RET_MBRID", OracleType.Number); mbrid.Direction = ParameterDirection.Output;
1
3753
by: Roger Barney | last post by:
I have a need to run a recursive CTE within a stored proc, but I can't get it past this: SQL0104N An unexpected token "with" was found following "SET count=count+1; ". Expected tokens may include: "". LINE NUMBER=26. My google-fu showed a couple of similar topics, but none with resolution. The query functions as expected outside of the stored proc, so I'm hoping that there's some syntactic sugar I'm missing that'll let this work....
0
9926
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
11095
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
10716
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
9552
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...
1
7952
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7108
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
5976
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4597
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
4194
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.