Trying to avoid a cursor. Please Help.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trying to avoid a cursor. Please Help.

Author

Topic

syoung68
Starting Member

6 Posts

Posted - 2006-01-11 : 09:55:51

I know there are solutions to variations of this, but I have not really seen this sepcifically.

I have to create a single field that will hold a categorized link list. Actually I have to update my email template with it. (see [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60094[/url])

My Table looks like this.


categoryID    category     linkName           linkID
1             catA         link name A        32
1             catA         Link-Name B.       33
2             catB         link name C        34
3             catC         link (name) D      35
3             catC         Link Name E Inc.   36

It gets slightly more complicated because i am calling another SP that formats linkName in a specific manner (formats for the url and takes out common words and characters) - call it formatForURL. This is already written.

The generated list need to look like this.

catA
<a href="http://www.abc.com/link-name-a/ID/32/page.aspx">link name A</a>
<a href="http://www.abc.com/link-name-b/ID/32/page.aspx">Link-Name B.</a>

catB
<a href="http://www.abc.com/link-name-c/ID/32/page.aspx">link name C</a>

catC
<a href="http://www.abc.com/link-name-d/ID/32/page.aspx">link (name) D</a>
<a href="http://www.abc.com/link-name-e/ID/32/page.aspx">Link Name E Inc.</a>

I hope this makes sense. I am try to avoid a cursor within a cursor.

There HAS to be another way. Yes, I know the opinions on cursors in general. DO I have to use a temp table with a heiarchy scheme. Is there a more direct approach?

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-01-12 : 14:38:07

I would think that you could easily migrate the SP that does the formatting to a User Defined Function such that the "main" query could do the work on the fly. Assuming you are using 2000 or 2005 and aren't still using SQL 7.0 or something. If you are on 2000/2005 then the question is ... Does it have to look "exactly" the way you have shown it? Reason I'm asking is that I think you could get very, very close to it using FOR XML EXPLICIT if you want to check that out in books on line.

syoung68
Starting Member

6 Posts

Posted - 2006-01-13 : 11:38:50

You are right I could migrate the other SP into the main query. There will be other items that use it and I was trying to cut down on repetitive code.

My bigger problem is the list itself. I have been trying FOR XML EXPLICIT and can't seem to get it to work. It won't see the columns in the the table... "The column prefix 'WEL' does not match with a table name or alias name used in the query."

"FROM [weeklyEmailLinks] WEL"

and if I leave off the alias I get "Invalid column name"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources