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

I'm not convinced that a SELECT * cursor for a 100+ column table is absolutely necessary, but if you're determined to do it and don't like to type then let the Object Browser help you out.

Right-click on the table in Object Browser and select "Script Table as CREATE To New Query Editor Window".
Replace everything down to the CREATE TABLE line with DECLARE.
Delete everything after the last column definition (indexes, constraints, triggers, permissions, etc).  Delete the trailing comma, if any, on the last line.
Use Replace All with Regular Expressions to turn the column definitions into variable declarations.
Get rid of all brackets: replace "\[{[a-z0-9_]+}\]" with "\1".
Prefix column names with @: replace "\t" with "\t@".
Remove NULL specifications: replace "{ NOT}* NULL" with "" (nothing).
Manually clean up any leftovers: IDENTITY, computed column expressions, TIMESTAMP declarations.  Fix any variable names that still have square brackets.

Add your basic cursor template: (the lines SELECT and INTO have a trailing space)

DECLARE xyz CURSOR FAST_FORWARD FOR
SELECT 
FROM dbo.table

FETCH NEXT FROM xyz
INTO 

In the object browser, click the + beside your table to expose the sub-items.  Left-click on the word "Columns" and drag-and-drop to the SELECT and INTO lines.

Turn the column names in the FETCH line into variables: replace " " (a single space) with " @" ("Use regular expressions" can be turned off).
Repeat any manual fixes made above to screwy column names.

It's a pretty simple process, although the description may look somewhat wordy.  It sure beats typing 100+ variable declarations.

Thank you Scott C. for the recommendation. I ended up copying all columns from the table and creating a macro to do similarly to what you recommended. It did the job pretty well. The sad thing is it seems no one knows for sure whether or not SELECT * is possible. If it is, how.

Thanks,

Tony

I want to know what will happen for the below scenario.

I have done some testing and found that it is a problem.

Now, I am declaring cursor for table, in a row by row fetch I am inserting a data that qualifies the select statement while declaring cursor.

For e.g. if my select statement gave me 4 records for salary > 1000 now for each of these 4 record I inserted one record with salary 1000+  in the same table then the records inserted newly should be 4. so the total records for  salary > 1000 should be 8 after the loop ends.

But this is not the case. The loop executes for random numbers in my case 886 to 122122 times.

Please suggest how to make sure Rows selected for cursor do not change with example. 

Thanks in advance

SOM,

I am not sure I understand your question 100%, but it sounds as if your SELECT statement that populates the cursor is incorrect.  If you just run the SELECT statement independent of the cursor, is it correct?

Thank you,
Jeremy Kadlec

Thanks Jeremy for the reply.

I got the solution to my problem and it is using STATIC CURSOR.

Below is what I was trying without static cursor.

Declare @name varchar(50)

Declare @level int

Declare @id int

Declare Cur_xx CURSOR FOR SELECT name,D_level FROM xx WHERE D_level = 2

OPEN CUR_xx

FETCH NEXT FROM CUR_xx INTO @name,@level

WHILE @@FETCH_STATUS = 0                     

BEGIN

Print 'NAME : ' + @name + ' : LEVEL : ' + cast(@level as varchar(10))

select @id = max(id) + 1 from xx

INSERT INTO xx(name,id,D_level) VALUES('NewValue1',@id,'2') 

FETCH NEXT FROM CUR_xx INTO @name,@level

END

CLOSE CUR_xx

DEALLOCATE CUR_xx

I just had to declare like this : Declare Cur_xx CURSOR STATIC FOR SELECT name,D_level FROM xx WHERE D_level = 2

SOM,

Great.  I am happy to hear it.

I plan on writing a tip about some of these additional features in the future.

Thank you,
Jeremy Kadlec

ORG_ID        START_DATE        END_DATE

222              1/1/2008              ?

222              2/28/2009             ?

222              4/1/2010               Default Value

How Can I find the End Date of first occurence og ORG_ID. End Date of first occurence shud be one date before 2nd occurence and a default value for the last occurence. Sql Server 2000.

Gary,

I am not sure I understand your question completely.  Are you expecting this to be your final data?

1/1/2008 to 2/27/2009
2/28/2009 to 3/31/2010
4/1/2012 and probably getdate()

Is there a column that unique identifies each row in this table?

Thank you,
Jeremy Kadlec

Hi Jeremy,

Yes this is what I am expecting my final data to look like. As per Org_ID

1/1/2008 to 2/27/2009
2/28/2009 to 3/31/2010
4/1/2012 and probably getdate()

Org_ID and StartDate and Price are the three fields I have in my table. Org_ID and StartDate combined make it unique.

I have to determine end date of the price for each Org_ID based on its start_date for different years.

Thanks

Gary

Hi jermy,

Your contributions are very helpfull for SQLDBA's in the world.I dont know how we thanks to you.

Regards,

Rajasekhar Reddy

cell No:+919966246368

 Dear Sir

please tell me iam working in the production support in using only one DQl tool for excuting sql query's then looking for a job on sql mean sql technical support or prodction support but i know very well oracle  pl/sql query's i don't know iam eligible for sql & unix prodcution support.please let know please clarify....

Tim Cullen

On your example (listed below) Isn't this same thing as using a cursor, I mean serial processing? Only difference I could see is that you are getting the data directly for the table.

Please let me know your Comments

Regards

Vinicio Aizpurua

Miami FL

TIM CULLEN's Example

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;

I'm trying to do an automated assigment process.  What I've is a list of loan processors:

Every night we receive new loans for processing.  In my query for the new loans I want to walk through each loan and assign a processor so my result set so my result set looks something like this when done each evening:

I've reied a plain CASE statement and that won't work.  This appears to me to require several cursors and variables.  Can someone please post a script that would accomplish this task?  Thank you

For loaning processing assignment query.

Something like the following would avoid cursors, and use just one variable. It is not very sophisticated, efficency could probably be improved. But, this is good enough for an example of avoiding RBAR (& cursors). It would help if there were usable indexes on a few of the columns too, but I've skipped that for an example.

NOTES:

1. % gives you a sequence that starts at 1, cycles through and ends in 0. Hence the -1 for [Assigned], to make te 2 sequences align.

2. You could make it more complex, to do the "round-robin assignment" in alphabetical order - bearin in mind above. Or you could replace % with a / and making a couple of other adjustments to get an in order list. But, % is simpler and hence a more reliable "assignment function".

CREATE TABLE loan(id INT, name NVARCHAR(256), processed BIT)
CREATE TABLE processor(id INT, name NVARCHAR(256), available BIT)

INSERT loan(id, name, processed)
VALUES(123456, 'Alice', 0),
(223456, 'Bob', 0),
(323456, 'Charles', 0),
(423456, 'Dave', 0),
(523456, 'Eric', 0),
(234243, 'Julie', 1),
(623456, 'Fran', 0),
(343434, 'Fred', 1),
(723456, 'Georgina', 0);

INSERT processor(id, name, available)
VALUES(1, 'Mel', 1),
(3, 'Andy', 0),
(5, 'Nathan', 1),
(6, 'Scott', 1);

DECLARE @avail int
SET @avail = (SELECT count(1) FROM processor WHERE available = 1)

SELECT loans.[New Loans], loans.[Loanee Name], processors.[Assigned To]
FROM (SELECT L.id [New Loans], row_number() OVER(ORDER BY L.id) % @avail [Assignee], L.name [Loanee Name]
      FROM loan L
      WHERE L.processed = 0) loans
JOIN (SELECT (row_number() OVER(ORDER BY P.name)) - 1 [Assigned], P.name [Assigned To]
      FROM processor P
      WHERE P.available = 1) processors
ON loans.Assignee = processors.Assigned
ORDER BY loans.[New Loans]

i have 3 questions.

1. write a pl/sql prog using triggers to copy the records of an employee into a backup table when that particular record is deleted.

2. write a pl/sql block that would handle the error when a wrong employee no is given as input or when a wrong amount is given as input(with triggers and without triggers)

3. using trigger see to that an employeee's commission never reaches above 1000.

i need the answer very fast..do hlp plz

Good tips.....and you have a great name!