Using the IN clause with a cursor

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

SQL Server Development (2000)

Using the IN clause with a cursor

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-14 : 07:17:12

Rob writes "I have a question regarding a cursor on SQL 2000.

I have a cursor defined as follows:

DECLARE cursorname_cursor CURSOR FOR

Select x, y from orgs o inner join projtest p on o.org_id = p.org_id
right outer join tests t
on p.test_id = t.test_id and p.proj_id = t.proj_id
where t.proj_id = @proj_id and t.prod_id = @Prod_id
and o.org_id in (@organic_id)
and t.test_no = @Test_no.

OPEN cursorname_cursor

WHILE @@FETCH_STATUS = 0

BEGIN

This cursor works fine if I take out the line that says

"and o.org_id in (@org_id)"

@Org_id is a string of values all seperated by single quotes such as '1', '2', '3'. I've tried passing this value in with and without the ( ) and with and without the beginning and end quotes, etc. (I can pass it in anyway that will work). Is there any way that I can make this work? It dies on the cursor when I go to Fetch the first row, @@Fetch_Status returns -1 for some reason.

I have made the select statement work using Dynamic SQL, but I don't know how to implement dynamic sql in a cursor like this

Thanks for any help"

gpl
Posting Yak Master

195 Posts

Posted - 2004-06-14 : 07:48:09

read this article http://www.sqlteam.com/item.asp?ItemID=11499

do you really have to use a cursor ?