Cannot fetch cursor into table variable

Question

I am trying to fetch each record of a table as a cursor and putting it into a custom table variable. I have kept the datatypes in the table variable same as the query result. But it doesn't seem to be working. Bellow is the code I am trying with.

Doesn't work:

DECLARE @MyCursor CURSOR;
DECLARE @r_field int;
DECLARE @r_DataSet TABLE
    (
        ID int,
        Full_Name nVarChar(max)
    );

BEGIN
    SET @MyCursor = CURSOR FOR
    select ID, custom_name as Full_Name from Students
    where isDeleted = 0

    OPEN @MyCursor 

    FETCH NEXT FROM @MyCursor 
    INTO @r_DataSet

    WHILE @@FETCH_STATUS = 0
    BEGIN
            --------------------------

            --Some code

            --------------------------

     FETCH NEXT FROM @MyCursor 
      INTO @r_DataSet 
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;

This code is giving me this error "Must declare the scalar variable "@r_DataSet"."

Although it does work when I try this with only one field without table variable.

Works:

DECLARE @MyCursor CURSOR;
DECLARE @r_field int;
DECLARE @r_DataSet TABLE
    (
        ID int,
        Full_Name nVarChar(max)
    );

BEGIN
    SET @MyCursor = CURSOR FOR
    select ID from Students
    where isDeleted = 0

    OPEN @MyCursor 

    FETCH NEXT FROM @MyCursor 
    INTO @r_field

    WHILE @@FETCH_STATUS = 0
    BEGIN
            --------------------------

            --Some code

            --------------------------

     FETCH NEXT FROM @MyCursor 
      INTO @r_field 
    END; 

    CLOSE @MyCursor ;
    DEALLOCATE @MyCursor;
END;

I am not very experienced in T_SQL queries. Any help would be appreciated.

UPDATE:

As asked by others I will explain what I want to achieve here by giving you an example. Please let me know if I am using a wrong approach altogether.

Well, say I have these following tables-

questions table

( ID, TITLE, DESC )

TAGS table ( ID, NAME, DESC )

So,

For each question in questions table, I want to search for the matching tags (could be more than one) in tags table and insert the question-tag mapping data in another temporary table. I already have all the functions defined to check if a particular TAG is valid for a particular QUESTION or, not. The problem I am having is traversing the questions table record by record and searching the tags table based on the current record. Just to be clear there is no way to join these two tables as both these tables hold different types of data.

I hope this example clears the problem statement to you.

As far as I remember cursors can't be fetched into table variables. However, cursors are usually the wrong solution to whatever the problem is. This is an XYPropblem. If you describe what is it that you try to do (I mean, what is the problem you try to solve), Someone will probably find a better solution for you. — Zohar Peled
– Zohar Peled, Commented May 25, 2017 at 7:02
@ZoharPeled all I want to do is for each record in the selected records from table X, I want to do some conditional search in Table Y and finally generate more than one result sets from the searched data. There are no matching fields in table X and Y on which I can JOIN these two tables. — Sudip
– Sudip, Commented May 25, 2017 at 7:41
@Sudip The real reason you are having problems is because you cannot fetch using a table - of any type. You can only fetch using scalar variables. And this is why your second example works while the first does not. In the future, any time you find yourself moving to wards solution that involves a cursor, you should stop. Use of a cursor is rare - it is often a crutch used by those who haven't yet figured out how to work with sets of rows. — SMor
– SMor, Commented May 25, 2017 at 13:03
@SMor you are right. Actually, as a beginner, I didn't know about stuff like cross apply. So, I tried to use the cursor and made it too complicated. Now I solved it using a cursor and it works just fine now. — Sudip
– Sudip, Commented May 25, 2017 at 14:34

cco · Accepted Answer · 2017-05-25 19:05:32Z

2

If the actual problem you are trying to solve is how to fill @r_DataSet with the results of the select statement, the solution is very simple:

DECLARE @r_DataSet TABLE
    (
        ID int,
        Full_Name nVarChar(max)
    );

insert into @r_DataSet
select ID, custom_name as Full_Name from Students
where isDeleted = 0

Update:

If the relationship between tables is determined by code in a SQL function, rather than data, you can use CROSS APPLY to invoke the function for each row in a SELECT, then use the result to populate your dataset.

edited May 25, 2017 at 19:05

answered May 25, 2017 at 7:28

cco

6,3712 gold badges20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Sudip Over a year ago

Hi @cco, this will not work for me as I need to traverse through each data of the table and depending on the current data I need to do some more search operation on another table and insert some data into a temp table as well. Please also see my update on the question itself.

cco Over a year ago

Can you give a more concrete example of the operations you want to do? There must be some relation between the two tables; often it is possible to translate these into set-wise operations, which SQL tends to handle better.

Sudip Over a year ago

Hi @cco, can you please check the updated question? I have given an example scenario as you have asked for.

cco Over a year ago

What do you mean when you say "search for matching tags"? That sounds like a possible join condition to me.

cco Over a year ago

Cross apply allows you to call the function for each row of your source table and return a value. If multiple values can be returned, use a table-valued function. You can then insert the results in the table variable.

|

Collectives™ on Stack Overflow

Cannot fetch cursor into table variable

1 Answer 1

Update:

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Update:

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related