Get Multiple Values in SQL Server Cursor

Get Multiple Values in SQL Server Cursor

This should work:

DECLARE db_cursor CURSOR FOR SELECT name, age, color FROM table; 
DECLARE @myName VARCHAR(256);
DECLARE @myAge INT;
DECLARE @myFavoriteColor VARCHAR(40);
OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @myName, @myAge, @myFavoriteColor;
WHILE @@FETCH_STATUS = 0  
BEGIN  

       --Do stuff with scalar values

       FETCH NEXT FROM db_cursor INTO @myName, @myAge, @myFavoriteColor;
END;
CLOSE db_cursor;
DEALLOCATE db_cursor;

How do I fetch multiple columns for use in a cursor loop?

Here is slightly modified version. Changes are noted as code commentary.

BEGIN TRANSACTION

declare @cnt int
declare @test nvarchar(128)
-- variable to hold table name
declare @tableName nvarchar(255)
declare @cmd nvarchar(500) 
-- local means the cursor name is private to this code
-- fast_forward enables some speed optimizations
declare Tests cursor local fast_forward for
 SELECT COLUMN_NAME, TABLE_NAME
   FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE COLUMN_NAME LIKE 'pct%' 
    AND TABLE_NAME LIKE 'TestData%'

open Tests
-- Instead of fetching twice, I rather set up no-exit loop
while 1 = 1
BEGIN
  -- And then fetch
  fetch next from Tests into @test, @tableName
  -- And then, if no row is fetched, exit the loop
  if @@fetch_status <> 0
  begin
     break
  end
  -- Quotename is needed if you ever use special characters
  -- in table/column names. Spaces, reserved words etc.
  -- Other changes add apostrophes at right places.
  set @cmd = N'exec sp_rename ''' 
           + quotename(@tableName) 
           + '.' 
           + quotename(@test) 
           + N''',''' 
           + RIGHT(@test,LEN(@test)-3) 
           + '_Pct''' 
           + N', ''column''' 

  print @cmd

  EXEC sp_executeSQL @cmd
END

close Tests 
deallocate Tests

ROLLBACK TRANSACTION
--COMMIT TRANSACTION

How do I select multiple columns in a cursor?

Think of your CURSOR as a selected set of data from the source tables that you will then iterate through.

Your snippet contains the hints to identify what is in the CURSOR, in your case refer to

-- Populate the cursor with your logic
-- * UPDATE WITH YOUR SPECIFIC CODE HERE *

In your case, your are trying to populate your CURSOR using the values from the variables you have declared using the statement:

SELECT @DateAdded,
            @IdEmployee,
            @EmailAddress,
            @Subject,
            @Message,
            @DateSent
FROM #tblLeaveNotifToApprover   

Now, since you have not populated those variables yet they are all NULL
What you then do is SELECT the variables that you have just declared back into the variables !!!!!

FETCH NEXT FROM db_cursor INTO @DateAdded,@IdEmployee,@EmailAddress,@Subject,@Message,@DateSent
WHILE @@FETCH_STATUS = 0

So it is little surprise that when you try to print these you get nothing returned.

Go back to your DECLARE CURSOR and ensure that you are actually selecting a data set to iterate through. a CURSOR should generally always be kind of like a temporary table. If we assume that your temp table #tblLeaveNotifToApprover has column names that match your variable names then you need:

DECLARE db_cursor CURSOR FOR 
SELECT DateAdded,
       IdEmployee,
       EmailAddress,
       Subject,
       Message,
       DateSent
FROM #tblLeaveNotifToApprover

Fetch multiple values from database

A cursor can be used as an iterator to iterate over rows in the result set. To get the first value from each row try this:

cursor.execute(sql)
values = [row[0] for row in cursor]

How to return multiple values from one function postgres

To return value to a function that returns table, we have to include RETURN QUERY statement inside function.

CREATE OR REPLACE FUNCTION get_students(classId int) 
    RETURNS TABLE (
        students_cursor refcursor,
        students_list text
) 
AS $$
DECLARE
    students text DEFAULT '';
    student record;
    cursor_students CURSOR(classId integer)
        FOR SELECT firstName, surname
        FROM students
        WHERE class = classId;
BEGIN

    OPEN cursor_students(classId);

    LOOP
        FETCH cursor_students INTO student;
        EXIT WHEN NOT FOUND;

    students := students  '  '  student.firstName  ' '  student.surname;
    END LOOP;

    CLOSE cursor_students;

    RETURN QUERY
       SELECT cursor_students,students;

END; $$ 

LANGUAGE 'plpgsql';

How to store multiple values in a SQL Server variable

You can declare a variable of type table

DECLARE @tblTrans TABLE (
    tranID INT
);

INSERT INTO @tblTrans 
SELECT DOCUMENT_SET_TRANSACTION.DOCUMENT_SET_TRANSACTION_ID 
FROM ESG.DOCUMENT_SET_TRANSACTION 
WHERE DOCUMENT_SET_TRANSACTION.IDENTIFIER 
    IN (SELECT identifiers FROM @envelopeId);

Depending on what you want to do with the values after this, you could declare a cursor to loop through them or select straight from the variable.

You could also look into using a temporary table depending on what scope you need.

---

Related Topics