[thelist] oh joy -- cursors and t-sql abound!
Chris Blessing
webguy at mail.rit.edu
Fri Jun 14 14:20:01 CDT 2002
Hi all-
I'm having a bit of difficulty with a particularly complex query I'm
running. To give you some background, this is SQL Server 7 on NT4 and I'm
working with cursors in stored procedures.
I'll spare you from the entire sp (it's quite long) but the problem I'm
having is with parameters that I want to pass into the sp itself. Take a
look at this cursor declaration:
DECLARE tnames_cursor CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT article_id, max(k.rank) rank
FROM paragraphs t1
INNER JOIN CONTAINSTABLE(paragraphs, paragraph_text, @term) k
ON t1.paragraph_id = k.[key]
WHERE article_id
IN(
SELECT article_id
FROM journal_article t1
INNER JOIN journals t2 on
t1.journal_id = t2.journal_id
WHERE
t2.productcode IN(2175,2208)
)
GROUP BY article_id
ORDER BY rank desc
Now, note that there's an "@term" variable being specified in the
CONTAINSTABLE() clause. That is the parameter I'm trying to pass into the
SP, however since I'm declaring the cursor FOR that query, I cannot use the
variable in that context. This is bad news for me since I need to be able
to specify a dynamic @term variable (it's the search terms specified for the
full-text search on the paragraphs table).
So what to do? Is there anything I _can_ do? I owe big for this one! TIA!
Chris Blessing
webguy at mail.rit.edu
http://www.330i.net
More information about the thelist
mailing list