is it possible to do this without cursors?

i have the following vb code that i want to turn into a stored procedure.
Can it be done without using cursors? thanks for any help!
what this code does is it says for each item, which other items reference it
in the column called source.
Set rst = CurrentDb.OpenRecordset("SELECT [Name], [Type], [ReferencedBy]
FROM [Catalog]")
If Not rst.EOF Then
rst.MoveFirst
Do While Not rst.EOF
objName = rst![Name]
objRefs = ""
Set findrst = CurrentDb.OpenRecordset("SELECT DISTINCT [Name],
[Type] FROM [Catalog] WHERE [Name] <> '" & objName & "' AND [Source] LIKE '*"
+ objName + "*';")
If Not findrst.EOF Then
findrst.MoveFirst
Do While Not findrst.EOF
objName = findrst![Name]
objType = findrst![Type]
objRefs = IIf(Len(objRefs) > 0, objRefs & ", " & objName
& " (" & objType & ")", objName & " (" & objType & ")")
findrst.MoveNext
Loop
End If
rst.Edit
rst![ReferencedBy] = objRefs
rst.Update
rst.MoveNext
Loop
End IfBen,
Please post the table DDL and sample data and desired results.
HTH
Jerry
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:97650560-E7C1-40A4-B7FA-0443DC0FB20F@.microsoft.com...
>i have the following vb code that i want to turn into a stored procedure.
> Can it be done without using cursors? thanks for any help!
> what this code does is it says for each item, which other items reference
> it
> in the column called source.
>
> Set rst = CurrentDb.OpenRecordset("SELECT [Name], [Type],
> [ReferencedBy]
> FROM [Catalog]")
> If Not rst.EOF Then
> rst.MoveFirst
> Do While Not rst.EOF
> objName = rst![Name]
> objRefs = ""
> Set findrst = CurrentDb.OpenRecordset("SELECT DISTINCT [Name],
> [Type] FROM [Catalog] WHERE [Name] <> '" & objName & "' AND [Source] LIKE
> '*"
> + objName + "*';")
> If Not findrst.EOF Then
> findrst.MoveFirst
> Do While Not findrst.EOF
> objName = findrst![Name]
> objType = findrst![Type]
> objRefs = IIf(Len(objRefs) > 0, objRefs & ", " &
> objName
> & " (" & objType & ")", objName & " (" & objType & ")")
> findrst.MoveNext
> Loop
> End If
> rst.Edit
> rst![ReferencedBy] = objRefs
> rst.Update
> rst.MoveNext
> Loop
> End If|||create table catalog (name varchar(255), type varchar(50), source text,
referencedby text)
sample data before running the stored procedure
name type source referencedby
red hat mens red shirt
red shirt mens
after the stored procedure runs, i need the table to look like
name type source referencedby
red hat mens red shirt
red shirt mens red hat
the end result says that the red shirt is referenced in the source column by
the red hat.
thanks for any and all help!
"Jerry Spivey" wrote:

> Ben,
> Please post the table DDL and sample data and desired results.
> HTH
> Jerry
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:97650560-E7C1-40A4-B7FA-0443DC0FB20F@.microsoft.com...
>
>|||SELECT c.[name], c.[type], c.[source], r.[name] AS ReferencedBy
FROM [catalog] c
LEFT JOIN [catalog] r ON c.[name] = r.[source]
HTH,
John Scragg
"Ben" wrote:
> create table catalog (name varchar(255), type varchar(50), source text,
> referencedby text)
> sample data before running the stored procedure
> name type source referencedby
> red hat mens red shirt
> red shirt mens
> after the stored procedure runs, i need the table to look like
> name type source referencedby
> red hat mens red shirt
> red shirt mens red hat
>
> the end result says that the red shirt is referenced in the source column
by
> the red hat.
> thanks for any and all help!
>
> "Jerry Spivey" wrote:
>|||A couple tips.
I assume you are not using the "text" data type for your source column. If
so, why? It is a FK column and should have the same data type as the related
column (in this case [name]). You can enforce referential integrity even
with self referenceing table relationships. I would suggest you do that.
Also, try not to use keywords for column or table names and try not to put
spaces in your column names.
Best of luck,
John
"Ben" wrote:
> create table catalog (name varchar(255), type varchar(50), source text,
> referencedby text)
> sample data before running the stored procedure
> name type source referencedby
> red hat mens red shirt
> red shirt mens
> after the stored procedure runs, i need the table to look like
> name type source referencedby
> red hat mens red shirt
> red shirt mens red hat
>
> the end result says that the red shirt is referenced in the source column
by
> the red hat.
> thanks for any and all help!
>
> "Jerry Spivey" wrote:
>|||Thank you for the reply but unfortuanately that doesnt work (i dont think)
because the column source can have any number of items in it that it
references. i guess my sample wasnt clear enough, let me try again
the column name is a database object name
the column type is the type of database object (user table, stored procedure
)
the column source is the source code for the object (eg, the code/text of a
stored procedure)
the column referencedby contains a list of objects where the value in this
records name field can be found in all other records source column
i hope that is a little clearer. i know the vb code i posted earlier works
for this exact task, but i was hoping to have a stored procedure version as
well that didnt use cursors.
thanks for any help again.
ben