[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