Cursor not working
-
I'm a relative novice at T-SQL, so take it easy on me.
Here's my problem: I am trying to use SQL CURSOR to update some records. The based on the DECLARE...FOR statement, I should have just three (3) records in my cursor for updating. However, when I run the script (see below), it updates the "Core" field for ALL of the records in the tsoSOLineExtAttribs_NCI table, NOT just the three that should be in the cursor.
==========================
DECLARE@CoreTextvarchar(10),-- Raw value of Core as TEXT
@CoreFloatfloat,-- Entire value of Core stated as number
@CoreIntint,-- Integer value of Core
@CoreModulofloat,-- Fractional value as number
@CoreFracvarchar(10),-- Fractional value as TEXT from lookup table
@CoreFracStringvarchar(10)-- Fractional value of Core as new string
DECLARE c4 CURSOR
FOR SELECT *
FROM tsoSOLineExtAttribs_NCI
WHERE (Core <> ''
AND CHARINDEX('.',Core) > 0)
ORDER BY SOLineKey
OPEN c4
FETCH c4
SET @CoreFloat= CAST(@CoreText AS Float)
SET @CoreInt= CAST(LEFT(@CoreText, CHARINDEX('.',@CoreText)-1) AS Integer)
SET @CoreModulo= @CoreFloat - @CoreInt
SET @CoreFrac= (SELECT FractionalValue
FROM tciFractionXRef_NCI
WHERE DecimalValue = @CoreModulo)
SET @CoreFracString = CASE @CoreInt
WHEN 0 THEN @CoreFrac
ELSE CONVERT(VarChar(3), @CoreInt) + '-' + @CoreFrac
END
UPDATE tsoSOLineExtAttribs_NCI
SET Core = @CoreFracString
CLOSE c4
DEALLOCATE c4
GO
===========================
What am I missing in my cursor handling?
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
update syntax using cursor is as follows
update <table name> set <condition>
where current of <cursor name>
it updates one row at a time. based on the position of cursor
-
Your update statement does not contain any WHERE condition.
-
You don't need a cursor to do this, of course. It's easy enough to do as just one update statement.
--Jonathan
--Jonathan -
Jonathan appears to be right, you could rewrite this as an update.
Forgive me if I am wrong, but it looks like you already know how to program in another language like ASP/VB or C++. Something to keep in mind when learning SQL is that it doesn't work the same way as procedural languages like VB. A lot of VB programmers try to treat SQL the same way and wind up over-using cursors when simple statements would be much easier to write (once you're familiar with the language, of course) and will execute 100s to 1000s of times faster.
You might try looking around this site for information on when to use, and when to avoid, cursors. TSQL is a very powerful language and can be made to do a number of things it wasn't meant to, but your database will generally perform a lot better if you do things the "SQL Way" whenever possible. Sorry to be so vague, but read up on "Set Processing" to learn about the advantages it has in SQL over using cursors.
I avoid cursors whenever possible, but I still find cases where they make the most sense. Just make sure that they are a last resort instead of your standard operating procedure. Oh, and never be afraid to ask questions; we all had to learn somehow. 🙂
-
quote:
You don't need a cursor to do this, of course. It's easy enough to do as just one update statement.--Jonathan
I believe you are right, but I get equally lost in dealing with all the CASE statements and (so far, anyway) I have found the IF construct in SQL somewhat confusing since there's no requirement for THEN and ENDIF.
Any suggestions in that regard--other than just lots of practice and trial-and-error?
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
These guys are right. You don't need a cursor to do the update. Also, let me suggest that you wrap the update statement in a transaction. BEGIN TRAN...COMMIT TRAN. When you are ready to run the code, use begin tran and the update statement without the commit tran. You should see how many records have been updated (you can even do a select statement first before commiting the transaction). When you are satisfied that the right number of records are effected, then run the COMMIT TRAN portion of the batch.
-
quote:
I believe you are right, but I get equally lost in dealing with all the CASE statements and (so far, anyway) I have found the IF construct in SQL somewhat confusing since there's no requirement for THEN and ENDIF.Any suggestions in that regard--other than just lots of practice and trial-and-error?
I guess there is nothing like practice.
I for myself learn things efficiently when running once or twice against the wall and get a bleeding nose
What about a good book on T-SQL like
'The Guru's Guide To Transact-SQL' by Ken Henderson ?
It contains lots of nice tricks
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
They way I've learned most languages is by needing to do something in the language that I don't know how to do yet. Then I'll hit up some web resources like Google or the language vendor's web site, search for tutorials, read language documentation, ask people that I bet know how, etc...
There are also a number of SQL books out there that can probably give you a good foundation in the basic syntax and let you know what SQL can, can't, and shouldn't do.
The CASE statement is a good example since it's not a control-flow statement like in VB. Look it up in BOL (Books OnLine in your SQL Server program group) for syntax, and keep in mind it is generally used in select and where clauses.
You should also look up the IF contstruct in BOL for it's proper syntax.
Something to remember when learning a new language is that a lot of the syntax is more decoration and incedental than it is purposeful. A good example is the Dim statement in VB. It stands for dimension, as in defining the dimension of an array, but you use it to declare all varibles... The "Then" and "EndIf" keywords are really just decorations that tell VB where to stop evaluating an If statement. Most languages will have their own flavor of basic statements. (in TSQL, you use BEGIN and END to delimit statement blocks, including IFs).
Read articles on this site and other SQL Server sites and check out some of the excelent web pages referenced in some signatures of some of the more experienced posters in these forums. There is a lot of good information out there.
-
quote:
These guys are right. You don't need a cursor to do the update. Also, let me suggest that you wrap the update statement in a transaction. BEGIN TRAN...COMMIT TRAN. When you are ready to run the code, use begin tran and the update statement without the commit tran. You should see how many records have been updated (you can even do a select statement first before commiting the transaction). When you are satisfied that the right number of records are effected, then run the COMMIT TRAN portion of the batch.Thanks! Good idea on using the Transaction wrapper.
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
quote:
quote:
I believe you are right, but I get equally lost in dealing with all the CASE statements and (so far, anyway) I have found the IF construct in SQL somewhat confusing since there's no requirement for THEN and ENDIF.Any suggestions in that regard--other than just lots of practice and trial-and-error?
I guess there is nothing like practice.
I for myself learn things efficiently when running once or twice against the wall and get a bleeding nose
What about a good book on T-SQL like
'The Guru's Guide To Transact-SQL' by Ken Henderson ?
It contains lots of nice tricks
Frank
Actually, Frank, I'm working my way through THE GURU'S GUIDE TO T-SQL right now. I've found it very helpful. Still, the fact that T-SQL accepts an array of constructs for the same functions (IF... THEN, CURSORS, CASE) makes it difficult to know if what is shown in the examples covers the "requirements" or just one approach. It also leaves me sometimes uncertain as to WHEN additional elements of the construct may be required--or valuable--in achieving my specific goal with the code.
For example, there is a "simple" construct for CURSORS that requires no WHILE... BEGIN... END but still loops through the cursor. However, I'm uncertain as to when I need the additional components to control (or assure) that a cursor acts the way I want it to.
Thanks for all the advice.
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
quote:
They way I've learned most languages is by needing to do something in the language that I don't know how to do yet. Then I'll hit up some web resources like Google or the language vendor's web site, search for tutorials, read language documentation, ask people that I bet know how, etc...There are also a number of SQL books out there that can probably give you a good foundation in the basic syntax and let you know what SQL can, can't, and shouldn't do.
The CASE statement is a good example since it's not a control-flow statement like in VB. Look it up in BOL (Books OnLine in your SQL Server program group) for syntax, and keep in mind it is generally used in select and where clauses.
You should also look up the IF contstruct in BOL for it's proper syntax.
Something to remember when learning a new language is that a lot of the syntax is more decoration and incedental than it is purposeful. A good example is the Dim statement in VB. It stands for dimension, as in defining the dimension of an array, but you use it to declare all varibles... The "Then" and "EndIf" keywords are really just decorations that tell VB where to stop evaluating an If statement. Most languages will have their own flavor of basic statements. (in TSQL, you use BEGIN and END to delimit statement blocks, including IFs).
Read articles on this site and other SQL Server sites and check out some of the excelent web pages referenced in some signatures of some of the more experienced posters in these forums. There is a lot of good information out there.
I learn much the same way. As Will Rogers put it (roughly restated)--There are three kinds of men in the world: Those who learn by reading; those who learn by observation; and those have to pee on the electric fence for themselves.
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC
Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC -
Well, in any case of uncertainty I would not hestitate to post a question here. Asking questions, IMHO, is a smart way of solving problems. Saves a lot of time and sometimes also a lot of mental health.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply