Loops?

[stevensteven]

I am just beginning with SQL Server stored procedures. Is there some way where I can do something like

Select * from mytable where ....

foreach row
begin
if(condition)
begin
Update......
end

end

 

[JohnDTampaBay]

Avoid loops if at all possible. A wise guru once told me, "Set-based is the true path, grasshopper." If you have to loop through a record set, look up DECLARE CURSUR in BOL. Be warned that CURSORs are very resource intensive. If you explain your scenario a bit, perhaps we can come up with a set-based solution. Good luck!

--Angel
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[SQLBill]

Why?

If you want to UPDATE data in a row based on some criteria just do:

UPDATE tablename
SET <change to be made>
WHERE <criteria here>

For example:

UPDATE mytable
SET mydate = '2004-03-05 00:00:00'
WHERE mydate = '2004-05-03 00:00:00'

or

UPDATE mytable
SET LName = 'Doe'
WHERE LName = 'Smith'
AND FName = 'Jane'

-SQLBill

 

[stevensteven]

My criteria is very extensive that I would have to check using many conditional statements, selecting from a number of different tables.

I believe a cursor is the solution.

Thanks for the help.

 

[cheyney]

You can write a user defined function and encapsulate all your criteria in there. Then you can still run a set-based update with advanced criteria

something like:

UPDATE mytable
SET mydate = '2004-03-05 00:00:00'
WHERE dbo.SuperIntensive('2004-05-03 00:00:00') = 1

Cheyney

 

[cheyney]

That being said, if you only need to run your update very infrequently, and efficiency or speed is not an issue, it might just be easier to write a cursor.

Good practice to do it the other way, though

Cheyney

 

[SQLSister]

I would never under any circumstances consider using a cursor for an update. Why? First, cursors are terribly expensive performance wise which may not be noticable at the time you are developing, but causes problems once there are lots of simultaneous users and many records. Second, even if I know that this particular situation will only loop through a few records, as Cheney says, this is the time to practice so that the set-based solution is the one you think of naturally thus avoiding efficiency sinks in your code later on because you are in a hurry and can't take the time to do something the correct way.

Looping through a cursor is only meant for those rare situations where you need to process one record at a time. Almost always these involve runnning some kind of system stored procedure and affecting meta data not the ordinary data in your database.

 

[cas85]

I am going down a similar path at the moment so any help would be appriecated. I am tring to repair a corrupted database. I need to create records in a table based on the existance on records in a second table.

I wrote the select and then thougt all I had to do was put an insert in front.

Insert into Mytable (Table2.Id)
Select from ID from Table2 where ID > 10.

This dose not work. I belive the correct way to do this would be to write a loop using a cursor. Can anyone confirm this and point me to some sample code?

 

[philhege]

cas85,

You just have a syntax error.

I bet if you run

Insert into Mytable (ID)
Select ID from Table2 where ID > 10

you should have some joy.

HTH,

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[cas85]

Maybe I over simplified it. First more than one record will be returned so the insert does not know what to do and second I get a Syntax error saying that column headings are not allowed as part of an insert.

 

[AtomicWedgie]

try it like this then:

select id
into MyTable
from Table2 where ID > 10

when u use
Insert into Mytable
select id from Table2 where id > 10

you first have to create the table Mytable.

 

[philhege]

Um, SQL is very capable for bulk inserts, using exactly the syntax I described. And, yes, the target table has to exist first. But I inferred from you original post that it did.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....

 

[cas85]

Will this work if I need to mix a column value with constant values?

Insert into Mytable (ID,"1.0000",Null,36,"Repaired Record")
Select ID from Table2 where ID > 10

Still looking for joy

 

[tlbroadbent]

The syntax for coilumn and constant values would be as follows.

Insert into Mytable (ID, col2, col3, col4, col5)
Select ID, '1.0000', Null, 36, 'Repaired Record'
From Table2 Where ID > 10

Use single quotes rather than double quotes.



If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.comcast.net/sql/sql_articles.htm