recursive call in stored proc - Microsoft SQL Server

Robert Song

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

Subscribe Reply

6150

John Gilson

"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

Robert Song

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

--CELKO--

>> 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

Similar topics

3242

call db2dari proc pb on V8.1 FP4

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

DB2 Database

7141

V5R2 DB2/UDB iSeries: can a system trigger call a SQL stored proc?

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...

DB2 Database

2272

Can't call Stored Proc... How to trace Flow of Web Application in VB.NET?

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...

ASP.NET

1828

Loops in stored proc or webcode? Which is most efficient?

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...

ASP.NET

1130

call stored proc on sql 2005 from 2000

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

Microsoft SQL Server

15781

Call stored proc inside another stored proc

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.

DB2 Database

3191

Recursive stored proc call

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()

DB2 Database

1561

Call stored proc from sql statement as subquery

by: skchonghk | last post by:

As title. Is it possible? Thanks. Adrian

DB2 Database

5700

return value from Oracle stored proc

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;

C# / C Sharp

3744

Using recursive CTE in stored proc?

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....

DB2 Database

8448

What is ONU?

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...

General

8871

Problem With Comparison Operator <=> in G++

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...

C / C++

8783

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

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...

Online Marketing

8552

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

4198

Trying to create a lan-to-lan vpn between two differents networks

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...

Networking - Hardware / Configuration

4369

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2773

transfer the data from one system to another through ip address

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

C# / C Sharp

2011

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

1776

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

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...

General