Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shifis
Posting Yak Master
157 Posts |
 Posted - 2003-09-01 : 14:42:48
|
| I have a friend that is asking me if I call process a result from a select row by row. He wants to uses cursor, but I am sure that is a better why to that.Thanks!!CREATE TABLE #TEMP_TABLE2(ITEM CHAR(32), NEXT_PRICE DECIMAL(13,5))INSERT INTO #TEM_TABLE2 (ITEM, NEXT_PRICE)Select item, next_price from TB_price--How can I do something like this??Do while !#TEM_TABLE2.eof print #TEM_TABLE2!item, #TEM_TABLE2!next_price #TEM_TABLE2.nextloop |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-09-01 : 16:58:46
|
| Ummmm, SELECT item, Next_Price FROM #temp_table2perhaps?Why would you want to use the PRINT statement anyway? |
 |
|
|
shifis
Posting Yak Master
157 Posts |
Posted - 2003-09-01 : 17:18:09
|
| Mmmm is what I said to my friend, but he come from Oracle backgrouns, and he is still confuse with the way SQL Server works.He said he want to process one by one the rows of the result from a select statement, he uses the print just as example. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-09-01 : 17:29:15
|
| Well, he picked up a number of bad habits from Oracle (row-by-row processing) Amazingly enough SELECT actually works with Oracle...you might want to inform your friend about that.In any case cursors are to be avoided in SQL Server as it is far better able to process sets of records using SQL statements. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-09-01 : 18:51:33
|
quote:
Mmmm is what I said to my friend, but he come from Oracle backgrouns, and he is still confuse with the way SQL Server works.He said he want to process one by one the rows of the result from a select statement, he uses the print just as example.
it depends on how you define "process". It's really important becuase that word can mean many things."return a value" = a SELECT can do it all at once"look up values in other tables and calc values" = a SELECT as well with JOINS"change a value in the table" = UPDATE can do it all at once"look up values in other tables and update the table" = UPDATE as well, with JOINS again"call a procedure and take some action" = you might need a cursor; but often you can re-write the action to work on sets of data all at once so you don't even need to "process" a table one row at a time- Jeff |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-09-02 : 04:14:54
|
quote:
Originally posted by robvolk
Well, he picked up a number of bad habits from Oracle (row-by-row processing) Amazingly enough SELECT actually works with Oracle...you might want to inform your friend about that.In any case cursors are to be avoided in SQL Server as it is far better able to process sets of records using SQL statements.
Sounds like his processing involves more than just a PRINT statement to be honest. As you said, the SELECT would do it all in one go but perhaps he's doing some supa dupa processing on each row that involves using a cursor?I don't know, but ever since I've come here I see nothing but people slagging off Cursors. Just the other day I had to use one and there was no other set based solution... and it wasn't that slow really!Will Microsoft make cursors more efficient in the next version?!Who knows...------------------------------------------------------------------------------I enjoy using SQL Server but I am not part of the Microsoft fanboy club! NEVER!! |
|
|
|
|
|
|
|
|
Process a select result one by one