Dear All,

I'm trying to learn how to user SPs with cursors to update data in tables. I have a test table called 'Table1' with a 'Name' column that has some test data like:

Jones, Seymour
Collins, Albert
Silver, John

I want to parse the name column and move the parsed first and last names to their respective columns: FirstName and LastName respectively.

Parsing is not the problem. When I run the stored procedure below it updates all first names and last names to the same first and last name; whatever the last record in the table is. In this case its setting all first names to 'John' and all last names to 'Silver'.

Can someone please point out the error of my ways?

Create Procedure ParseName2
as
Declare
@thename varchar(50),
@LastName varchar(50),
@FirstName varchar(50),
@wherecomma int,
@totalLen int

DECLARE Names_Cursor CURSOR FOR
Select Name from table1

OPEN Names_Cursor

FETCH NEXT FROM Names_Cursor

WHILE @@FETCH_STATUS = 0
BEGIN
Select @thename = Name from table1
Set @wherecomma = CHARINDEX(',',@thename)
Set @totalLen = len(@thename)
Set @lastname = left(@thename,@wherecomma - 1)
Set @firstname = ltrim(rtrim(right(@thename,@totalLen - @wherecomma)))
Update table1 Set Firstname = @FirstName, LastName = @LastName WHERE CURRENT OF Names_Cursor
   FETCH NEXT FROM Names_Cursor
END

CLOSE Names_Cursor
DEALLOCATE Names_Cursor
GO

exec parsename2

Thank you,
Richard

Hi Richard,

1) You seem to be not sure on what you update with what? ;) Even I am not sure about that, by your code.

2) When you do a select to assign a variable, you should see to that it returns a single value, else the last value of the select statement would be assigned to it. This where you ERR first.

Select @thename = Name from table1
When you say this, as per the data you listed, @thename would have Silver, John since it is the last value. (Jones is assigned first, then overwritten with Collins, and finally Silver)

3) I don't see usage of a CURSOR in this operation. May be if you can explain on that I would be in a position to point out the errors in your ways and suggest alternate solution too.

But I don't see any flaw in the flow of code. So I think you can choose someother problem where cursor can be used and try that.

Say, you have re-ordered 1000 quantity of all of your items, and you can try updating its Quantity_on_Hand + 1000 row by row using a cursor and also display its price * Quantity as CurrentValue for each record.

You can see Books online for cursor, which has got good examples too.

Hope that helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection

Hi,

Thank you for the response.

Well, maybe I don't even need a cursor for this operation at all. I thought I would have to have a cursor to do what I wanted to do. Is there some way to update the 'FirstName' and 'LastName' fields with data from the 'Name' field without a cursor, parsing it and all?

Otherwise, I'm trying to select the 'Name' field row by row parsing the value and updating that row with the parsed values for the 'FirstName' and 'LastName' fields.

Here's the table if it would help to clarify.

CREATE TABLE [dbo].[Table1] (
    [rowid] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
    [Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Status] [int] NOT NULL
) ON [PRIMARY]
GO

I have been in the BOL and this is about as far as I've been able to get. I did create an SP that would parse these values and update but I could only get that to work by inputing a rowid.

Your help is much appreciated.

Richard

I have made changes to your cursor there. You can take a look at it.

The line in Green was not needed at all.
The additional changes are done in Red

Create Procedure ParseName2
as
Declare
@thename varchar(50),
@LastName varchar(50),
@FirstName varchar(50),
@wherecomma int,
@totalLen int

DECLARE Names_Cursor CURSOR FOR
Select Name from table1

OPEN Names_Cursor

FETCH NEXT FROM Names_Cursor INTO @thename

WHILE @@FETCH_STATUS = 0
BEGIN
 -- Select @thename = Name from table1
Set @wherecomma = CHARINDEX(',',@thename)
Set @totalLen = len(@thename)
Set @lastname = left(@thename,@wherecomma - 1)
Set @firstname = ltrim(rtrim(right(@thename,@totalLen - @wherecomma)))
Update table1 Set Firstname = @FirstName, LastName = @LastName WHERE Name = @thename
   FETCH NEXT FROM Names_Cursor INTO @thename
END
CLOSE Names_Cursor
DEALLOCATE Names_Cursor
GO

Cheers!

_________________________
-Vijay G
Strive for Perfection

Dear HappyGV,

Now I'm happy too. Yes, yes. It works.

You have shown me the error of my ways. That's it. I just couldn't figure out how to get the row value into the cursor. And then you added the value of the cursor 'Name' into the where statement.

That is awesome.

Thank you so very much.

Cheers!
Richard

Hi Happy,

You know, you're absolutely right. This operation doesn't need a cursor. It can be done with a bulk update.

update table1
set LastName = left(Name, (charindex(',',Name) - 1)), FirstName = ltrim(rtrim(right(Name,(len(name) - CHARINDEX(',',name)))))

Richard

Hi Richard,

Yes, you are right, that is what I was referring in my previous post and why I suggested to try cursor with other scenario, as that has two things(update and select) to be done.

One - Update all records with quantity + 1000.
Two - Show the Currentvalue = Price*Quantity.

So in your case, it was just a single operation, which can be done using single update statement. So cursors are suitable only when you do more than one operation. But usually Cursors are not recommended as it hits the performance of the SQL server. So one should try and avoid that as much as possible. But nothing wrong in knowing how it works.;)

Performance Tuning SQL Server Cursors

Cheers!

_________________________
-Vijay G
Strive for Perfection

Hi Happgv,

Yes, I was making it much more complicated than it had to be and I've heard of the detrimental effects in performance of cursors.

I just couldn't wrap my brain around the bulk update at first.

But, what you've given me is so helpful in understanding cursors if I should ever HAVE TO USE ONE.

Thanks!

Cheers!

Richard