Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 27th, 2004, 06:36 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default Help with Update Cursor in SP

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



Reply With Quote
  #2 (permalink)  
Old June 27th, 2004, 09:51 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old June 27th, 2004, 10:31 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

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








Reply With Quote
  #4 (permalink)  
Old June 27th, 2004, 10:40 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #5 (permalink)  
Old June 27th, 2004, 10:50 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

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



Reply With Quote
  #6 (permalink)  
Old June 27th, 2004, 11:09 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

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

Reply With Quote
  #7 (permalink)  
Old June 27th, 2004, 11:23 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #8 (permalink)  
Old June 27th, 2004, 11:39 PM
Friend of Wrox
Points: 2,101, Level: 18
Points: 2,101, Level: 18 Points: 2,101, Level: 18 Points: 2,101, Level: 18
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: , , .
Posts: 599
Thanks: 6
Thanked 3 Times in 3 Posts
Default

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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Magnetic Cursor - Target Area Cursor? gcarcass .NET Framework 2.0 1 May 5th, 2008 07:20 AM
sp update error 1222 Gunny SQL Server 2000 1 August 21st, 2006 12:47 PM
SP W/Cursor OUTPUT parm: 2nd and later calls fail hfreeman SQL Server 2000 0 August 24th, 2003 01:31 AM
Can a SP run another SP as sa? dbradley SQL Server 2000 0 July 17th, 2003 08:35 AM



All times are GMT -4. The time now is 07:51 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.