One thing that should be added... if you're going to use a cursor and the results do not have to be sensitive to change and you don't need to move back and forth within the set, then look at using the FAST_FORWARD option which is a low overhead, read only, forward only type with no tempdb overhead.  It runs through the resultset sequentially.

I think another point to mention is to limit the amount of information obtained for use with the cursor.  I see a number of people who select everything from a table even though they only need two fields. 

 Ahhhh, yes. Good one, Tim.  I can't tell you how many times I've seen SELECT * used when declaring a cursor.

We are using a script very similar to this.  It uses exec msdb.dbo.sp_send_dbmail to email our clients appointment confirmations.  

A sql view has been written that calls the data set correctly.   Some clients have two appts the same day for different reasons and at different times. Most emails are sent correctly, with the correct data. 

But these records for clients that have two appts are sending only the data for one of the appts, and then sending it twice.  Any ideas?  Is there a flush command that can solve this?

Cheers,

Eric

Eric,

My first inclination would be that the query you use to populate the cursor is not getting the correct data.  If you just run the SELECT query in the 'Declare Cursor' portion of the code, are you getting the correct data? 

If not, this code needs to be amended.

If so, can you provide some sample data and the code?

Thank you,
The MSSQLTips Team

Jeremy,

thanks for the tips. It helped a lot.

Cheers,

Douglas Brum

I have an EMPLOYEE table which consists of empID, empLastName, empFirstName, empAddress1... columns.

How do I reference a specific column in a cursor using SELECT * from EMPLOYEE?

Thank you very much.

Example code:

DECLARE emp_cur CURSOR FOR
SELECT * FROM Employee
ORDER BY empID;

OPEN empl_cur
FETCH NEXT FROM emp_cur
WHILE @@FETCH_STATUS = 0
   BEGIN
      -- PRINT empID, empFirstName, empLastName here
   END
CLOSE emp_cur;
DEALLOCATE emp_cur;

Unless you absolutely have to use every column in the table for the cursor activity I would avoid SELECT * statements.  You should be able to reference the column by name.  Did you get an error when you attempted to execute the code above?

Tony:

Here is an example of using a non-cursor loop to get the information:

SET NOCOUNT ON;

DECLARE @Transactions TABLE

(EntryID INT IDENTITY(1,1) PRIMARY KEY, TransactionTypeID INT, TransactionType VARCHAR(150))

DECLARE @Start INT = 1, @End INT, @TransactionTypeID INT, @TransactionType VARCHAR(150)

INSERT INTO @Transactions (TransactionTypeID, TransactionType)

SELECT TransactionTypeID, TransactionType FROM dimTransactionType WITH (NOLOCK)

SELECT @End = @@ROWCOUNT

WHILE @Start <= @End

BEGIN

SELECT @TransactionTypeID = TransactionTypeID, @TransactionType = TransactionType 

FROM @Transactions WHERE EntryID = @Start

PRINT @TransactionTypeID

PRINT @TransactionType

SELECT @Start += 1

CONTINUE

END

SET NOCOUNT OFF;

Tony,

I apologize for my delayed response.  I think this might be the code you are looking for.

DECLARE @empID INT
DECLARE @empFirstName varchar(50)
DECLARE @empLastName varchar(50)

DECLARE emp_cur CURSOR FOR
SELECT empID, empFirstName, empLastName
FROM Employee
ORDER BY empID;

OPEN emp_cur
FETCH NEXT FROM emp_cur INTO @empID, @empFirstName, @empLastName

WHILE @@FETCH_STATUS = 0
   BEGIN
       
 -- PRINT empID, empFirstName, empLastName here
 
 FETCH NEXT FROM emp_cur INTO @empID, @empFirstName, @empLastName

   END
CLOSE emp_cur;
DEALLOCATE emp_cur;

Thank you,
Jeremy Kadlec

Tim,

Thank you for the quick response and alternatives.

Thank you,
Jeremy Kadlec

Thank you Tim and Jeremy for your responses. The code I submitted was only an example. I'd really prefer to use SELECT * statement as I have to use and process more than 100 columns in the table. I really don't want to list them all multiple times (DECLARE, SELECT, FETCH NEXT (twice)).

When I referred to empID as emp_cur.empID, I got the error "SQL Server Database Error: The name "emp_cur.empID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Thanks again for your help.

Tony

Tony,

To take a step back, what are you ultimately trying to do?  Issue a simple UPDATE, INSERT or DELETE statement?  ETL process?  Data import or export?

If I understand your first question correctly, you would need to reference each column when you create a variable, populate the cursor, iterate the row set if you are processing each column in some sort of manner. 

If I understand your second question correctly, you would need to reference the variable @empID.

Thank you,
Jeremy Kadlec

 i want to use cursor in this table ..want to fetch value from id through cursor..please help me immediately..

 i want to use cursor in this table ..want to fetch value from id through cursor..please help me immediately..

WHY do you need a cursor, Deepak?   What post processing do you need to do on the data that can't be done using a SET operation?

Cursors should never be your first choice for data access.  

yes sir you are right..But i want to understand the concept of cursor with a simple and easy example...

please help me sir with better eaxample through which i can understand the concept easily...as i am the beginner in SQL Server...

I'm not sure if you've received my post as I don't see it above. Here it is again and I appologize if I've posted it twice.

I have to write a process that reads Employee records, update other tables and insert into another table for other processes. Before updates and inserts can be done, I'd have to do a lot of data manipulations. I guess my question should be:

If I MUST use select * from employee cursor, how would I go about referencing a column (i.e. employee.empID) from the cursor without having to declare vairables and fetch into them?

Below is the equivalent in ORALCE:

for emp_cur in (select * from employee order by empID) loop

   str_empFormalName := emp_cur.empLastName || ', ' || emp_cur.empFirstName || ', ' || emp_cur.empMiddleInitial;

   update tempEmployee1
      set empFormalName = str_empFormalName;
    where empID = emp_cur.empID;

    insert into tempEmployee2 (empID, empLastName, empFirstName, empMiddleInitial, empFormalName)
       values (emp_cur.empID, emp_cur.empLastName, emp_cur.empFirstName, emp_cur.empMiddleInitial);

end loop;

As you can see from above, I've named my cursor emp_cur and used select * from employee. When I want to reference a column, I qualify the column name with the cursor name (i.e. emp_cur.empID), and I do not have to use variables.

Thanks,

Tony

Tony,

Did you see these two posts?

• Monday, April 16, 2012 - 12:48:56 PM - Tim Cullen
• Friday, April 20, 2012 - 11:53:22 AM - Jeremy Kadlec

Did they help point you in the right direction?

If the issue is creating variables for each column, I would try to use Tim's code as an example.

HTH.

Thank you,
Jeremy Kadlec

Deepak,

Did these two sections in this tip make sense to you:

• Example SQL Server Cursor
• SQL Server Cursor Components

Please let me know.

Thank you,
Jeremy Kadlec

Thank you both Tim and Jeremy. I really would like to use SELECT * in the cursor and avoid using variables just like my example in Oracle. But if I have to use variables then...

Thanks again for your time.

Tony