3

I have a cursor defined as:

DECLARE idCursor CURSOR         
    FOR SELECT DISTINCT [id], [data]
        FROM #tempTable  

And I have a variable defined as:

DECLARE @currentId TABLE (
                            [id] int,
                            [data] char(1)
                         );  

But when I try to fetch the next result into @currentId

FETCH NEXT FROM idCursor INTO @currentId  

I get this error:

Must declare the scalar variable "@currentId".  

How can I get the result of a cursor fetch in a table? I understand that I can declare two variables and store the results individually. But for a table with many columns that would be time consuming.

6

No, you can't fetch scalar values from a cursor row into a table variable. You would have to declare the variables, fetch into them, and then insert:

FETCH NEXT FROM idCursor INTO @id, @data;
INSERT @currentId([id],[data]) SELECT @id, @data;

However, perhaps it's the case that you don't need a cursor at all. Why are you processing one row at a time? Why not populate the @table variable with whatever query originally populated #tempRemaining?

INSERT @currentId([id], [data])
SELECT DISTINCT [id], [data]
    FROM #tempTable;

Or even just using #tempTable by itself and skipping the table variable altogether? Maybe even skipping the #temp table? A cursor combined with a table variable and a #temp table just sounds like a nightmare.

At the risk of sounding like Celko, this seems very much like flat file processing from the 1970s... and it's even worse that there are so many columns that declaring those variables would be prohibitive.

  • Thanks. I will try to explore non-cursor solutions. But I do need to process rows individually. See my original problem – hazrmard Jul 23 '15 at 20:41
  • 2
    The accepted solution in your original problem does not require a cursor – Nick.McDermaid Apr 23 '18 at 23:05
  • @Nick.McDermaid Yup, thanks for telling me. I changed the accepted answer for my original problem once a better one came along. – hazrmard Oct 15 '18 at 15:27
0

Simple example using cursor:

DECLARE @CustomerID as INT;
declare @msg varchar(max)
DECLARE @BusinessCursor as CURSOR;

SET @BusinessCursor = CURSOR FOR
SELECT CustomerID FROM Customer WHERE CustomerID IN ('3908745','3911122','3911128','3911421')

OPEN @BusinessCursor;
    FETCH NEXT FROM @BusinessCursor INTO @CustomerID;
    WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @msg = '{
              "CustomerID": "'+CONVERT(varchar(10), @CustomerID)+'",
              "Customer": {
                "LastName": "LastName-'+CONVERT(varchar(10), @CustomerID) +'",
                "FirstName": "FirstName-'+CONVERT(varchar(10), @CustomerID)+'",    
              }
            }|'
        print @msg
    FETCH NEXT FROM @BusinessCursor INTO @CustomerID;
END

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.