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.
| Author |
Topic |
|
AQSIT
Starting Member
1 Post |
 Posted - 2004-06-11 : 09:59:13
|
| 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_cursorWHILE @@FETCH_STATUS = 0BEGINThis 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 thisThanks for any help |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2004-06-11 : 10:03:31
|
| The FAQ (http://www.sqlteam.com/faq.asp) has a link to three articles on "dynamic SQL" that will show you why it won't work. It will also show a way around it but it's a bit more complicated.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
 |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2004-06-11 : 10:04:42
|
| And I almost forgot about this article which addresses your exact situation (http://www.sqlteam.com/item.asp?ItemID=11499).===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
|
|
|
IN statement within a cursor