How do I select multiple columns in a cursor?-CodePudding

How do I select multiple columns in a cursor? I tested the below code but it's returning/printing nothing.

DECLARE @DateAdded VARCHAR(50)
DECLARE @IdEmployee NVARCHAR(20)
DECLARE @EmailAddress VARCHAR(100)
DECLARE @Subject VARCHAR(50)
DECLARE @Message VARCHAR(100)
DECLARE @DateSent VARCHAR(50)

-- 2 - Declare Cursor
DECLARE db_cursor CURSOR FOR 
    -- Populate the cursor with your logic
    -- * UPDATE WITH YOUR SPECIFIC CODE HERE *
    SELECT 
        @DateAdded, @IdEmployee, @EmailAddress,
        @Subject, @Message, @DateSent
    FROM #tblLeaveNotifToApprover

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @DateAdded, @IdEmployee, @EmailAddress, @Subject, @Message, @DateSent

WHILE @@FETCH_STATUS = 0  
BEGIN  
    -- 4 - Begin the custom business logic
    -- * UPDATE WITH YOUR SPECIFIC CODE HERE *
    PRINT CONVERT(VARCHAR,@DateAdded)

    FETCH NEXT FROM db_cursor INTO @DateAdded, @ IdEmployee, @EmailAddress, @Subject, @Message, @DateSent
END 

CLOSE db_cursor  
DEALLOCATE db_cursor

Initially wanted to print all that columns in each row, but after running the snippet/code it nothing displays, I also tried converting the variables to varchar same result is thrown.

I expect that all the columns of my 7 rows will be print out.

CodePudding user response：

This is wrong

DECLARE db_cursor CURSOR FOR 
-- Populate the cursor with your logic
-- * UPDATE WITH YOUR SPECIFIC CODE HERE *
SELECT 
    @DateAdded, @IdEmployee, @EmailAddress,
    @Subject, @Message, @DateSent
FROM #tblLeaveNotifToApprover

You need to replace the variables in this SELECT statement with the actual column names from the table. You don't refer to your local variables until the FETCH statement.

CodePudding user response：

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