By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

recursive logic with sql server

P: 1
I was able to write a recursive logic in c# and because of performance issue I need to write this in Stored Proc or UDF in SQL Server. I have following tables and I would appreciate if any one can suggest me how to do something like this
Table EQ- ID, Name, SYSID (nullable)
Table RD - ID, Name
Table EQ_RD_Xref - EQ_RD_XrefID, EQID, RDID (EQ and RD have many to many relationship)
Table SMAST - MASTERID, MasterName
Table EQ_SMAST_XREF - EQ_SMAST_XREF,MASTERID,EQID
Table SYS - SYSID, SYSName

Whenever I want to get the dependencies for EQ BY ID it should give me entire forest of EQ's (tied to EQ_SMAST_XREF and these EQ's may have SYS or other RD's), RD's (these RD's may have some other EQ's) and Sys. The forest should have only the items in the forest and not any other item
Thanks for any help in advance
Jan 7 '10 #1
Share this Question
Share on Google+
3 Replies


Delerna
Expert 100+
P: 1,134
Recursive logic is handled in TSQL via the Cursor, so try searching SQL servers help documentation for "Cursor".

Bear in mind that, for performance reasons, the use of cursors is frowned upon.
There are situations where a cursor is the only means by which a task can be accomplished but these are rare. It is almost always possible to do something via vastly more efficient, "set" based queries where the recordset is processed as a whole instead of one record at a time.
Jan 11 '10 #2

nbiswas
100+
P: 149
Also you can use Recursive CTE's if you are using Sql Server 2005+

Here is an example

SQL SERVER – Simple Example of Recursive CTE
Mar 24 '10 #3

nbiswas
100+
P: 149
You may also find interest in Recursive Stored Procs.

Using recursion in stored procedures
Mar 24 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.