I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!
View 4 Replies
I have a table describing a hierarchy structure and the number of levels is very large, say 10000. Can I control the recursive CTE to get the first 1000 levels? Thanks!
Msg 240, Level 16, State 1, Line 14
Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,
DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),
[code]....
I am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod
View 4 Replies View RelatedHi there, Any tips on my problem would be most welcome...
right, the scenario.
A web Blog
blogger1 posts a blog_entry, e.g I love the simpsons
blogger2 comments on that blog_entry, e.g No, I hate the simpsons
Blogger3 comments on that comment, ie, How can you hate the simpsons.
so you can comment on a comment on a comment etc.. lool.
right, i have got two tables... Blog_entry & comment. i need to be able to search for a blog_entry + all the comments on that blog_entry..
at the moment i can search for the comments on the blog_entry using the FK in the comment table.
blog_entry
INSERT INTO Blog_Entry VALUES(0001,'I love the Simpsons');
comments
INSERT INTO Comment VALUES(0001,' No I hate the simpsons, cID 1000);
but i need to be able to search for the comments on comments
INSERT INTO Comment VALUES(0001,' How can you hate the simpsons, cID1000);
hopefully you can see the problem here, with only one comments table how can i get the search for the comment on the comment.. there’s nothing linking them...
I could make a sub comments table, and use a FK (as with the blog_entry & first comment)
but then I would have to make another sub sub table to be able to get those comments on the first sub table... this would go on and on for each comment on comment.
you can see the cID1000 (comment PK) I can't use this to get the comment because its duplicating the PK...
So, I need to be able to search for the comments on comments… eg. I need to be able to search for blogger2, and any comments that were made on his comments.
Someone I know mention using recursion to get the comment on comment info, is this right ?
Hehe, I hope you understand what im asking here… the is my first exploration of SQL, so any tips, hints, would be most welcome….
Thanks loads…
PS: if there anything you don’t understand about what I have written, or what im asking… please say so…
Spence.
I have a table
CREATE TABLE [dbo].[chart_hiera2](
[AccessID] [int] NULL,
[ChildID] [int] NULL,
[Child] [varchar](100) NULL,
[ParentID] [int] NULL,
[CGID] [int] NULL,
[Depth] [smallint] NULL,
[Lineage] [varchar](255) NULL,
[node] [bit] NULL,
[PercentOwnership] [varchar](10) NULL,
[Notes] [varchar](80) NULL
) ON [PRIMARY]
I am trying to build the value off hierarchy that will be later inserted in the linage and Depth column. I am trying to do so using recursion . the rulles for recording the linages as as follows Lineage = parent.Lineage + Ltrim(Str(ParentID,6,0)) + '/'
here is my code below
with BuildHierarchy as (
SELECT AN.AccessID,AN.ChildID,AN.Child,AN.ParentID,AN.CGI D, 1 as Depth,AN.Lineage,AN.node,AN.PercentOwnership, AN.Notes
FROM chart_hiera2 as AN WHERE AN.Depth Is Null and AN.AccessID = @accID
union all
SELECT AN.AccessID,AN.ChildID,AN.Child,AN.ParentID,AN.CGI D,Cast(AN.Depth as smallint) +1 ,Cast(BH.Lineage+ Ltrim(Str(AN.ParentID,6,0)) + '/' as varchar(255)),AN.node,AN.PercentOwnership, AN.Notes
FROM chart_hiera2 as AN inner join BuildHierarchy BH on AN.ParentID=BH.ChildID
WHERE AN.Depth>=0 AND AN.Lineage Is Not Null AND AN.Depth Is Null and AN.AccessID = @accID)--@accID --and T.AccessID = @accID)
select * from BuildHierarchy
but it does not increment Depth or builds Lineage .What am I doing wrong?
How to write a recursive Procedure in SQL Server to find factorial of 50?
The recursive call is limited to 32..could any one help me out.
Thanks in Advance
Guys,
I desperately need your help.
what i need to do is call a function getdate to return dates recursively
like so
classdate=getdate(class#, repeat, sportcategory,date)
while (classdate <yearend)
begin
classdate=getdate(class#, repeat, sportcategory, classdate)
end
I want to use the date returned and keep calling that function till the yearend is reached, to get a bunch of dates
declare @startDate datetime
with cteStartDate as
(
select x= classdate,
repeat,
class#,
sportcategory,
yearend
from sports
union all
select x=getdate( classdate,
repeat,
yearend),
repeat,
class#,
sportcategory,
yearend
from cteStartDate
where x < cteStartDate .yearend
)
I tried using cte for this, it only returns one date for everything, as opposed to an array of dates, how do i solve this problem.Any
insight will be greatly apprecaited.
Thanks
Hi there! I have an application that uses stored procedures wuth CTE statements to populate trees from database. Every second I need to populate 100-300 trees each one of them has 15-20 nodes. I checked those procedures with profiler and payed attention that populate procedure with CTE is slower that others (without CTE) per 1000 times! Would procedures with recursion faster than same ones with CTE? I am asking because I read somewhere that CTE is becomes slower while populating little chunks of data. Is that right and I should use recursions?
And second question: here the code -
Code Snippet
;WITH dt (Id, NodeText, PId, State, RowNumber) AS
(
SELECT Id, NodeText, PId, State, ROW_NUMBER() OVER(ORDER BY Id) AS RowNumber
FROM DataTable
WHERE Id = 1
UNION ALL
SELECT d.Id, d.NodeText, d.PId, d.State, ROW_NUMBER() OVER(ORDER BY d.Id) AS RowNumber
FROM DataTable AS d
INNER JOIN dt ON dt.Id = d.PId
)
SELECT * FROM dt
This is how I populate a tree with CTE. Could you give me some example how to do exactly same but with recursion?(without CTE). Thank you!
Hi Y'all,I receive an error while using recursion:The maximum recursion 100 has been exhausted before statement completion. Can someone tell me where i can alter the default value?Thanks in advance
View 3 Replies View RelatedHello
Maximum nesting level of recursion in SQL Server 2000 is 32. How much is maximum nesting level in SQL Server 2005?
Thanks
I have a multi-level folders table, named folders with attributes of folder_id, parent_id and user_id, and have another table, users, contains all the user_id. I need to list all the users for each folder which has parent_id = 0 and its all sub-folders' users into one table. I have created a function to return a table with folder_id and user_id for one single folder. However, I don't know how to use this function to get the sub-folder's users and merge them together as one single table.
Here is my function:
CREATE FUNCTION [dbo].[FolderUsers] (@fid int) RETURNS Table AS Return (select folder_id, f.user_id from folders f, users u where f.user_id = u.user_id and folder_id = @fid)Go
where @fid is the top folder with parent_id = 0 at here, the next level sub-folder's parent_id would be = @fid.
I am thinking to have recursive call from the parent_id = @fid that returns another table and have to concatenate to the called table. I have been thinking of store procedure, "insert into" and so on, but don't know how to implement it.
Do you have any good inspiration for me? Thank you in advance!
// I need to write a query to extract data from two tables where I save information on my tables in one and the relationship between the tables in the second.
//CREATE TABLE [dbo].[tblPages](
// [PageID] [int] NOT NULL,
// [PagePath] [varchar](max) ,
// [MenuName] [nchar](10),
// [Directory] [varchar](15))
//CREATE TABLE [dbo].[tblPageRelation](
// [PageRelationshipID] [int] NOT NULL,
// [TopPageID] [int] NOT NULL,
// [BasePageID] [int] NOT NULL)
// What I would like to do is do the following in SQL is a combination of this
// SELECT * FROM TblPageRelation PR LEFT OUTER JOIN TblPages P on PR.TopPageID = P.PageID
//WHERE P.DIRECTORY = 'MENUBLOCK' UNION SELECT * FROM TblPageRelation PR LEFT OUTER JOIN TblPages P on PR.TopPageID = P.PageID
//WHERE P.DIRECTORY = P.DIRECTORY
//public void recursivemove (String Directory)
// {// Find Rows that Have this Block Name associated with em
// For each returned row find the rows that share the same Directory
// }
//}
I am not sure if select recursion is possible and thought I would throw this challenge to the dba community. The preference would be to create a view that does the work on the backend instead of writing frontend VB code.
Below is script that creates and populates a temp table along with the desired result-set.
create table #myTest
([id] int identity (1,1),
[Parent] int ,
minutes smallint
)
insert into #myTest (parent,minutes) values (null,1)
insert into #myTest (parent,minutes) values (1,2)
insert into #myTest (parent,minutes) values (1,4)
insert into #myTest (parent,minutes) values (3,8)
Desired resultset:
id Parent Totalminutes
----------- ----------- -------
1 NULL 15
2 1 2
3 1 12
4 3 8
Hi Everybody, I have a table that contains two field Group_ID, ParentGroup_ID this maintains parent Child relation ships between groups now I need a stored procedure in SQL Server that would give the ID's of all Parents of a specified group. it is possible through recursion in stored procedure. I have not much idea of recursion in procedure. Can anybody help, by providing a related sample or code? I would be highly thankful.
Sample
ParentGroup_ID Group_ID
12 8
13 8
14 9
15 12
16 25
0 15
How can I find all the parents of 8 i.e. 12,13,15,0 through procedure and in same format.
RegardsDheeraj Verma
I've encountered some strange behavior in a recursive procedure I'mwriting for a bill of materials. First let me ask directly if what Ithink is happening is even possible:It seems like the procedure is not following the recursion in serialorder, but in parallel. In other words, after one instance of theprocedure calls itself, it continues executing lines below therecursion before the recursion is done. Is that possible? I lookedfor SQL Server Options that might deal with recursion or threading butI couldn't find anything.Now let me explain what's happening in terms of the BoM. All the rowsI expect are returned, but not in the correct order. Let's assume thefollowing tree:1|-2| |-5| | |-7| | -8| -6| -9|-3| |-10| |-11| | |-13| | -14| | |-15| | |-16| | -17| -12| -18| -19| -20| |-21| -22-4-23|-24-25-26This is stored in table P using MemberID and ParentID fields. Forexample,MemberID ParentID-------- --------1 NULL2 13 14 15 26 2(etc...)Based on how I wrote the recursion (I will provide the procedurebelow), I would expect output when starting from MemberID of 1 to looklike this:MemberID Depth Sort-------- ----- ----2 1 15 2 27 3 38 3 46 2 59 3 6(etc... basically, the line order of the graphical tree above, or acounter-clockwise traverse around the tree)Instead, I get this (I'll provide the whole thing because I don't seea pattern):MemberID Depth Sort-------- ----- ----2 1 15 2 23 1 210 2 37 3 34 1 36 2 39 3 423 2 48 3 411 2 413 3 512 2 524 3 525 3 618 3 614 3 615 4 719 4 726 4 720 5 816 4 817 4 921 6 922 6 10Call me crazy, but it looks like my tree was parsed in the same orderthat a set of dominos arranged in the same shape would topple. Theonly way I could see that happening is if the recursion is non-linear,allowing both children and siblings to be parsed simultaneously. Itwould also explain why my sort counter didn't increment properly, butthe depth counter is always correct.Now here are the procedures. There's also a Qty column, since this isa BoM after all, but I didn't need to mention it for my illustrationof the problem above.CREATE PROC makebom @root bigint---- This would be called by the client to find all the parts andquantities-- under a specific part (@root)--ASSET NOCOUNT ONCREATE TABLE #result (MemberID bigint, Qty bigint, Depth bigint, sortbigint)EXEC bomrecurse @root, 1, 0SET NOCOUNT OFFSELECT MemberID, Qty, Depth, sort FROM #result ORDER BY sortGOCREATE PROC bomrecurse @root bigint, @depthcounter bigint,@sortcounter bigint---- This is the recursive procedure, called once by makebom, butrecalling-- itself until the whole tree is parsed, filling the #result table--ASDECLARE @memberid bigint, @qty bigint, @nextdepth bigintDECLARE children_cursor CURSOR LOCAL FORSELECT MemberID, Qty FROM PWHERE ParentID = @rootORDER BY MemberIDOPEN children_cursorFETCH NEXT FROM children_cursorINTO @memberid, @qtyWHILE @@FETCH_STATUS = 0BEGINSET @sortcounter = @sortcounter + 1INSERT INTO #result VALUES (@memberid, @qty, @depthcounter,@sortcounter)SET @nextdepth = @depthcounter + 1EXEC bomrecurse @memberid, @nextdepth, @sortcounterFETCH NEXT FROM children_cursorINTO @memberid, @qtyENDCLOSE children_cursorDEALLOCATE children_cursorGOI'm surprised this even worked as well as it did because I'm a newbiewhen it comes to stored procedures and I put this together fromexamples I found around this group, online and in the T-SQL Help. Sofeel free to comment on other aspects of my code or approach, but I'mmost interested in understanding the behavior of this recursion.
View 4 Replies View RelatedI have a bit of an issue with an app I'm working on. The app integrates two different SQL Server applications - both of which employ recursive triggers to some extent. My integration basically serves to establish a link between different tables in the applications, and maintains consistency between the two applications (where common fields exist) by employing INSERT, UPDATE, and DELETE triggers.
In order to prevent infinitely recursive triggers (AppA.TableA's update trigger updates AppB.TableA. AppB.TableA's update trigger updates AppA.TableA...and so on, and so on) I need to be able to somehow selectively prevent these triggers only from executing recursively. For example, if the trigger in AppA is what calls the trigger in AppB, I do not want AppB's trigger to fire (and vice versa).
Further Information:
The apps may be on the same, or different, SQL servers.
I'm fully aware of the database-wide options to disable trigger recursion (ALTER DATABASE), but I can't disable recursion for the balance of the triggers in the databases.
The integration will run on either SQL 2000 or SQL 2005 - and perhaps one server on 2000, and one on 2005 (depending upon the deployment).
I'm perfectly amenable to handling it in the trigger code, if possible, but I'm at a bit of a loss as to how to properly and efficiently manage that.
I know that SQL Server will kill infinitely recursive triggers once it detects them, but that doesn't exactly solve my original problem.
Thanks very much for any input you can offer.
Hello my friendsThis is my sql table structureFK = ID int, Empnaam varchar(200), PK = EmpID int With this table, where i insert values, employees can hire other employees. Now i want to see with a function in visual studio who hired who. I get stuck when a person hired more than 1 person....This is my stored procedure :set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROC [dbo].[ShowHierarchy] ( @Root int ) AS BEGIN SET NOCOUNT ON DECLARE @EmpID int, @EmpNaam varchar(30) SET @EmpNaam = (SELECT EmpNaam FROM dbo.Emp WHERE EmpID = @Root) PRINT REPLICATE('-', @@NESTLEVEL * 4) + @EmpNaam SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE ID = @Root) WHILE @EmpID IS NOT NULL BEGIN EXEC dbo.ShowHierarchy @EmpID SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE ID = @Root AND EmpID > @EmpID) END END Thanks in advance!Grtz
View 8 Replies View RelatedI have a table (folderstructure) with the following columns:
pcmid, cmid, foldername
pcmid is the parent directory
cmid is the directory
foldername is the name of the directory
e.g. note, number of levels are unknown
cmid pcmid name
1 NULL c:
101 1 level1
201 101 level2
45 101 level2a
56 201 level3
57 201 level3a
I'm looking to create a table that has cmid followed by the full directory path
So either (using above):
cmid path
1 c:
101 c:level1
201 c:level1level2
45 c:level1level2a
56 c:level1level2level3
57 c:level1level2level3a
etc.
OR
cmid 1 2 3 4
1 c:
101 c: level1
201 c: level1 level2
45 c: level1 level2a
56 c: level1 level2 level3
57 c: level1 level2 level3a
etc.
I've can use recursion to allocate a level to each name /cmid/pcmid combination
I could use multiple self joins
Is there a way this can be achieved using pivots or CTE recursion or something else...
I have a table :
id + A + B
1 |0,11| 0
2 |0,45| 0,5
3 |0,85| 0,75
I need to calculate the following :
F = 0,85 * ( 1 - 0,75 * ( 1 - 0,45 * ( 1 - 0,5 * ( 1 - 0,11 * ( 1 - 0 )))))
In which F = A3 * ( 1 - B3 * ( 1 - A2 * ( 1 - B2 * ( 1 - A1 * ( 1 - B1 )))))
It seemed quite easy at first glance. I Built it up via string concatenation and thought to execute the dynamic sql with sp_exec and get the result. As I don't like dynamic sql I was wondering If there is any other way..
ALTER PROC [dbo].[Tools_Serial_FE]
AS
BEGIN
DECLARE @IP FLOAT,
@IF FLOAT,
@FE FLOAT,
[Code] ....
Hi All,
I am placing a Matrix inside the table control for grouping requirements,but when we export the report to the Excel, the contents inside the table cell are ignored. Is there any way to get the full report exported, as per the Requirement.Please help me with this issue.
With Thanks
M.Mahendra
does any one have and example of how to embedd a flash swf file onto a report.??? Is it possable? any examples would be helpful.
View 1 Replies View RelatedUPDATE #2: When it said "Do you want to install Microsoft SQL Server" I said "yes" and that caused it to work. I exited and re-ran and now the print runs w/o the "install SQL Server" (If the prompt had said "Do you want to install the print dialog" we wouldn't be having this discussion...)
UPDATE: After posting this i discovered that the same thing occurs when attempting to print the report direct from IE6: First a dialog pops up "Do you want to install this software?" Name: Microsoft SQL Server. When I click "Don't Install" I get the dialog "unable to load client print control." Since this happens direct from IE6 I suspect it's browser settings. I'll resume tomorrow and post a followup.
My WinForm C# app integrates Reporting Services by calling them from WebBrowser controls. The problem is attempts to print cause a dialog: "unable to load client print control."
I've read prior posts that say "enable Active-X in your browser" - I don't know how to do that from a WebBrowser control.
Any ideas how to support Reporting Services "Print" from within a WebBrowser control?
RELATED THREADS
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=332145&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=264478&SiteID=1
I need to help in writing stored procedure to recursively delete Categories and ads for those categories
Simplified table views:
Category: CategoryID, Name, ParentCategoryID
Ads: AdID, Name, CategoryID
Please help
Hello Everyone,
I have a purchase order table that holds say 2 columns. PO and OrgPO. That is Purchase Order # and Original Purchase Order # respectively. Assume i have the following rows in the table.
PO OrgPO
-- ------
po1 NULL
co1 po1
co2 co1
co3 co2
po2 NULL
cpo1 po2
po3 NULL
Now what i would like to report in the output is the PO and along with the lastly generated change order for that po. For eg,
PO LastCO
-- ------
po1 co3
po2 cpo1
po3 po3
Currently i 'm using function to achieve this effect and i believe this is not the efficient way. I would like to generate this in a much efficient way. Please help me to achieve this.
Hi
I have problem is getting list of all the Tree Level Employees.here is the my table structure
Manager ~ SubOrdinate
1 ~ 2
1 ~ 3
1 ~ 4
2 ~ 5
2 ~ 6
2 ~ 7
3 ~ 8
3 ~ 9
4 ~ 10
5 ~ 11
SO ON
I NEED THE WAY , HOW I CAN GET THE HIRE
LEVELS ( EG IF I PASS MANAGER(EMPLOYEE NO) 1 IT SHOULD DISPLAY HIS 1 LEVELS AND 2, 3 SO ON LEVELS OF LEVELS
OUTPUT COULD BE ,
PASSED EMPLOYEE NO : 1
LEVEL ~ EMPLOYEE
1 ~ 2
1 ~ 3
1 ~ 4
2.1 ~ 5
2.1 ~ 6
2.1 ~ 6
3.1 ~ 7
SO ON THAT EMPLOYEE RELATED INFORMATION
( ITS LIKE MLM LEVELS)
CAN ANYBODY HELP IN THE ,
THANKS
is there any way to define a recursive cte within another cte. I have defined a recursive cte which works great for finding the different versions of a given item. What I would like to do is to define a set of items for which I want to find the different versions.
View 1 Replies View Related
Hello,
I have the following tables:
Article(articleID,CategoryID,ArticleTitle)
Categories(categoryID,ParentID,CategoryTitle)
I am trying to retrieve the main category ID for a specific article ID.
For example lets say I have this data:
Article:
1, 10 , "some title"
2,10,"some title"
3,11,"some title"
Categories:
1, NULL , "some title"
2, 1, "some title"
10, 2, "some title"
11, 10 , "some title"
In this example I want to know who is the main category of article 3.
The query should return the answer: 1
Thats because:
The article ID 3 is inside category 11.
Parent for category 11 is 10.
Parent for category 10 is 2.
Parent for category 2 is 1
and Parent for category 1 is NULL, which means category 1 has no parents and it is the main category.
Query will return article id, category id, main_category_id, ArticleTitle, CategoryTitle (some union between 2 tables)
Do you have any suggestions for such query?
Thanks all.
Recursive quey to show products with "custom defines fields" related by Classifications, instead of per product Hello, I’m working on a project .. .
I’m desperating due to the
complex (for me and also I think for some others) sql query that I need to
write, to show the products with his “custom defined fields� that are inside a ProductsFieldsByClassification
table that holds this mentioned “custom defined fieds� according to the Classifications
table, on where the Products can be found trought the productsClassifications
table.
CustomFields can be defined
and set for the products, trought his Classifications (instead of define a
custom field for each product (that consume a lot of data), I decide to use it as
I explain)
I will to know the properly
SQL QUERY to show a list of products with the ProductsFieldsByClassifications and ProductsFieldsValuesByClassifications:
As example on
a Requested ID_Classification = 16 (Torents/Games/Dreamcast/PAL), the products must be show with the
ProductsFields and Values that has the DBA for the:
·
requested
ID_Classification
o
PAL (ID_Classification: 16)
·
AND all
the Classifications that belongs above (trought ID_ParentClassification) that are :
o
Torrents (ID_Classification:
1) that will show the products values for the “Size�
o
Games (ID_Class..:4) Ăź this classification has no CustomFields so none from this one.
o
Dreamcast (ID_Class..:14 )
that will show his ID_Classification(14) product field “Levels� value (but not “AllowSave� as not have value for any product)
Hmnn i show a graphic that i design for (feel to click over to see at correct resolution)
I also write asp.net tutorials. For those interested see my blog at http://abmartin.wordpress.com
i have a table like this
parentid | childid | description
1 2 blah
1 3
1 4
2 23
2 24
5 8
3 10
and i want to give the parentid 1 and get all the children
i have a cursor now like this but i dont know how to make it recursive any help?
1
2 DECLARE @childid nvarchar(50)
3 DECLARE ItemStruc CURSOR FOR
4
5 SELECT cmponent_prt_no , parent_part_no
6 FROM oauser_prod_structure
7 WHERE parent_part_no = @parentid
8
9 OPEN ItemStruc
10 FETCH NEXT FROM ItemStruc
11 INTO @childNum , @parentid
12
13 WHILE @@FETCH_STATUS = 0
14 BEGIN
15
16 print @childNum +'is the a child to: ' +@parentid
I have a tough issue with a query.
I have the following structure
Table: Users
UserId ParentUserId
1 1
2 1
3 2
4 1
5 4
6 5
I need to write a stored procedure that takes in UserId as parameter and returns everyone under him/her.
So if Param=1 it would return result set of 2,3,4,5,6
Anyone done this before or have any ideas?
Thanks,
ScAndal
Hi,
Does anyone know how to do an sql recursion queries?
I believe it involves a view with a union.
I have a User Table and in that table i have a employee_id and a
boss_id. What i'd like to do is to find all employees under a certain
boss.
For example,
Employee_ID Boss_ID
1
2 1
3
4 3
5 2
So if i'd like to know who are under the employee_id = 1 it will return
employee_id 2 and 5 since employee 2 also is the boss of employee_id =
5.
To do that i'd have to have recursion query.
Thanks,
I have a list of categories that I have to find the path to each my table is set up like ID CategoryNum CategoryName ParentID 1 AA Top Level 1 02 AB Top Level 2 03 BA Second Level 1 1I need my query/stored proc to return Tope Level 1/Second Level 1 if I pass in the integer of 3I can do this in programming but cant seem to wrap my head around it in SQL ServerTIA for the help
View 6 Replies View Related