Help with Cursor

Question

Post reply

Help with Cursor

brekher

SSCommitted

Points: 1514
More actions
February 15, 2008 at 9:57 am

#183119

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.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

You'll get better help if you post your table DDL along with some sample data and your desired results.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Jeff Moden SSC Guru Points: 1004447 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

brekher SSCommitted Points: 1514 More actions · Answer 3

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.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

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?

brekher SSCommitted Points: 1514 More actions · Answer 5

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

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?

Jeff Moden SSC Guru Points: 1004447 More actions · Answer 7

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 8

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/

brekher SSCommitted Points: 1514 More actions · Answer 9

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.

Jeff Moden SSC Guru Points: 1004447 More actions · Answer 10

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

brekher SSCommitted Points: 1514 More actions · Answer 11

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 🙂