Cant fetch record from Cursor

Hi,

I'm relatively inexperienced in sql, and am having trouble interpreting the behavior of a cursor in some code I have inherited. When there is a record in both the Filters and FilterElements tables, the fetch_status is 0. If there is a record in Filters, but no child record in FilterElements, the fetch_status is -1. Since the tables are joined with a RIGHT OUTER JOIN, even when there is no corresponding record in FilterElements, a record is returned (I have verified running the select in a query window). But when used in a cursor, the record is not fetched. The fetch_status is -1. Can anyone tell me why the fetch doesn't work in this case. Thanks

--

DECLARE @.CreatedByUsernchar(100),@.WorkflowIDsvarchar(50);

DECLARE @.MyVariableCURSOR;

SET @.MyVariable=CURSORFOR

SELECTisnull(Filters.WorkflowIDs,''),

isnull(FilterElements.CreatedByUser,'')

FROMFiltersRIGHTOUTERJOIN

FilterElementsON Filters.ItemID= FilterElements.FiltersItemID

WHERE FiltersItemID= @.FilterID;

OPEN @.MyVariable;

FETCH NEXTFROM @.MyVariable

INTO @.WorkflowIDs, @.CreatedByUser;

For Table A and B,

1. if you dont have any corresponding entries for a record in A in B, and still wants to display the record in A, you should be using LEFT OUTER JOIN

2. if you dont have any records in B for a record in A, but have records for other records in A and still wants to display all the records in B, then you should be using RIGHT OUTER JOIN

Thanks

-Mark the post(s) as "Answer" that helped you|||

Everything seems to be correct. I had tried using the steps u followed, I am getting results for that,

DECLARE @.storeID varchar(10), @.storeName varchar(50), @.ordNo varchar(10)
DECLARE @.MyVariable CURSOR
SET @.MyVariable = CURSOR FOR
SELECT isnull(a.stor_id,''), isnull(a.stor_name,''),isnull(b.ord_num,'') from stores a
right outer join sales b on a.stor_id = b.stor_id
Open @.MyVariable
fetch next from @.MyVariable into @.storeID,@.storeName, @.ordNo
while @.@.fetch_status = 0
begin
print @.storeID+''+@.storeName+' '+ @.ordNo
fetch next from @.MyVariable into @.storeID,@.storeName, @.ordNo
end
close @.MyVariable
deallocate @.MyVariable

|||

Thanks. It appears to be working now. I think I got the LEFT and RIGHT outer join confused. That's a newbie for you.