February 15, 2008 at 9:57 am
I have a user doing a simple select like 'Select general_id from TableA where id =1'
This will bring back like 5 id's. I then need to go and loop through the same table and rerun the same select but instead of 1, I would need to use each of the new ids from the result set. This will bring back an addtional and different 100 id's and it goes on. They want this to run until no more ids are available. Thats why I am assuming a cursor is the best approach. Can anyone help out with this? Thanks.
February 15, 2008 at 10:24 am
You'll get better help if you post your table DDL along with some sample data and your desired results.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
February 15, 2008 at 12:29 pm
I get what you want to do... but, I'm with John... I need more info, please...
Also, what do you mean by "unitil No ID's are left"? Are you saying you want to go up to the 2.14 billion limit of INT????
--Jeff Moden
February 15, 2008 at 12:57 pm
I am working with the developers to get the exact results they are looking for, they basically want to narrow it down so that the result set would actually be the parent id and child id with description. I will post their sample result set with table ddl once I get the final confirmation. Thank you for your help so far.
February 15, 2008 at 1:06 pm
Also - knowing what they plan on doing iteratively with these little sets of rows might be helpful. there might not be a "good reason" why they need the data spoon-fed somewhere. That smells a bit like "procedural process being applied to a database" which is often a bad, bad idea.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 19, 2008 at 9:37 am
Hi,
Ok, I have the exact requirements and will post the ddl to the 2 tables that are involved along with the select being run now. Any help would be greatly appreciated. Thanks!
We need to have a SPROC created for searching. I need to pass in two parameters, the root ParentCID from where we will start the recursion, and then the string we are searching for.
The order of operations should be as such:
1) Execute the following query, and then process a LIKE on the C.CNAME column to see if the search string passed in is contained (LIKE searchString%).
2) Use each of the returned CIDs and again execute the same query, replacing the root CID with the CID from the result set.
I need two columns in the result set returned to me. A comma separated lists of CIDs, from parent to child such as:
24709,24826,24878
24709,802,3346,742
24709,24797,742
And comma separated names for those CIDS:
Private Passenger Auto,Underwriting Info, Date of birth of the operator
SELECT C.CID, C.CNAME, CL.CLASSLINKID, CL.PARENTCID, CL.CLASSLINKTYPEID FROM ClassLink CL INNER JOIN Class C ON Cl.CID = C.CID WHERE CL.Parentcid = 24709 ORDER BY CNAME
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Class](
[CID] [int] IDENTITY(1,1) NOT NULL,
[SuperclassCID] [int] NOT NULL,
[CName] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DName] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Description] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Notes] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ImplementedBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ConfiguredBy] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IsAbstract] [bit] NULL,
[LoadOnDemand] [bit] NOT NULL,
[ClassTypeValueID] [int] NOT NULL,
[ClassPath] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DateCreated] [datetime] NULL,
[LastModifiedDate] [datetime] NULL,
[LastModifiedBy_IDPSystemUserID] [int] NULL,
[RecordTimeStamp] [timestamp] NOT NULL,
[EffectiveVer] [int] NULL,
[ExpirationVer] [int] NULL,
[Effective] [datetime] NULL,
[Expiration] [datetime] NULL,
[EventText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
---------Table 2
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ClassLink](
[ClassLinkID] [int] IDENTITY(1,1) NOT NULL,
[CID] [int] NOT NULL,
[ParentCID] [int] NOT NULL,
[ClassLinkTypeID] [int] NOT NULL,
[ClassLinkTypeModifierID] [int] NULL,
[IsPersistant] [bit] NOT NULL,
[IsMultiple] [bit] NOT NULL,
[IsAggregate] [bit] NOT NULL,
[Notes] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EffectiveVer] [int] NULL,
[ExpirationVer] [int] NULL,
[Effective] [datetime] NULL,
[Expiration] [datetime] NULL,
[DateCreated] [datetime] NULL,
[LastModifiedDate] [datetime] NULL,
[LastModifiedBy_IDPSystemUserID] [int] NULL,
[RecordTimeStamp] [timestamp] NULL,
[Temp_CID] [int] NULL,
[Temp_ParentCID] [int] NULL,
[NoLoadForRoot] [bit] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
February 19, 2008 at 9:47 am
Ahhh - a hierarchy.
While you might do it with a cursor - it probably would be better to use an iterative process using a temp table like the one described in this Microsoft example, for expanding your hierarchy.
Here's the link:
http://support.microsoft.com/kb/248915
this should yield some serious perf gains versus using a cursor to do this, especially if your subtree has the potential to "get big".
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 19, 2008 at 3:00 pm
Hmmm... I see what you want to do... this is just a small example... if you really want commas in your final output, see where I marked the code "Modify this section".
--=======================================================================================
-- Setup some test data... note that nothing in this section is part of the actual
-- solution.
--=======================================================================================
--===== Setup a "quiet" environment
SET NOCOUNT ON
--===== Create a table to hold some test data.
-- This is NOT part of the solution
CREATE TABLE #yourtable
(
ID INT,
ParentID INT,
Descrip VARCHAR(20)
)
--===== Populate the test table with 2 "trees" of data
INSERT INTO #yourtable
(ID,ParentID,Descrip)
SELECT 9,NULL,'County 1' UNION ALL --Note NULL, this is top node of "Tree 1"
SELECT 2,9 ,'C1 Region 1' UNION ALL
SELECT 4,9 ,'C1 Region 2' UNION ALL
SELECT 3,2 ,'C1 R1 Unit 1' UNION ALL
SELECT 5,2 ,'C1 R1 Unit 2' UNION ALL
SELECT 6,4 ,'C1 R2 Unit 1' UNION ALL
SELECT 7,NULL,'County 2' UNION ALL --Note NULL, this is top node of "Tree 2"
SELECT 8,7 ,'C2 Region 1' UNION ALL
SELECT 1,9 ,'C1 Region 3'
--=======================================================================================
-- The following code makes a Hierarchy "sister" table with strings that are used
-- to traverse various hierarchies.
--=======================================================================================
--===== Create and seed the "Hierarchy" table on the fly
SELECT ID,
ParentID,
Descrip,
Level = 0, --Top Level
HierarchyString = CAST(STR(ID,5) AS VARCHAR(8000))+' '
INTO #Hierarchy
FROM #yourtable
WHERE ParentID IS NULL
--===== Declare a local variable to keep track of the current level
DECLARE @Level INT
SET @Level = 0
--===== Create the hierarchy in the HierarchyString ****Modify this section
WHILE @@ROWCOUNT > 0
BEGIN
SET @Level = @Level + 1
INSERT INTO #Hierarchy
(ID, ParentID, Descrip, Level, HierarchyString)
SELECT y.ID,y.ParentID,y.Descrip, @Level, h.HierarchyString + STR(y.ID,5) + ' '
FROM #yourtable y
INNER JOIN #Hierarchy h
ON y.ParentID = h.ID --Looks for parents only
AND h.Level = @Level - 1 --Looks for parents only
END
--=======================================================================================
-- Now, demo the use of the sister table
--=======================================================================================
--===== Display the entire tree with indented descriptions according to the Level
SELECT ID,
ParentID,
Level,
LEFT(REPLICATE(' ',Level*2)+descrip,30),
HierarchyString
FROM #Hierarchy
ORDER BY HierarchyString
--===== Select only the "downline" for ID 2 including ID 2
SELECT ID,
ParentID,
Level,
LEFT(REPLICATE(' ',Level*2)+descrip,30),
HierarchyString
FROM #Hierarchy
WHERE HierarchyString LIKE '% 2 %'
ORDER BY HierarchyString
drop table #Hierarchy
drop table #yourtable
--Jeff Moden
February 19, 2008 at 5:58 pm
brekher,
A cursor would certainly work, but what you may want to look at is putting it in a temp table. Then take the top one value, processing it, and then deleting and repeating it until your temp table is empty. This does effectively create a hidden cursor in a way, but depending on the situation the code is often easier to read and faster.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
February 27, 2008 at 12:07 pm
I've been attempting to get this working with my tables that I have. Still need some help. Could you please post, attach or email the actual solution for my tables that I had posted above? Thanks.
February 27, 2008 at 1:05 pm
Ummm... the example I posted is pretty clear... I'm thinking that you should be able to cut and paste a couple of table and column names on your own...
--Jeff Moden
February 27, 2008 at 1:25 pm
Thanks, I probably missed something and will go over. I was just going based on your top line of this is only a small example of what needs to be done and thought that there was more for me to add to this that I was not able to figure out. I can definitly copy and paste on my own, thanks again for your help 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply