SQL Statement without Cursor

[kolla123]

Could some please help me to write a SQL statememt to run a user defined stored procedure for each row in a table. I don't want to use cursor.

Thanks,
kolla123

 

[JohnDTampaBay]

It may depend on what you want the stored procedure to do as to whether or not you have to use a cursor. If the stored procedure is deterministic, then convert it to a user-defined function and call the function in an UPDATE statement like any other function.

For example, if you want to populate the STATE column given the ZIP code:

UPDATE MyTable SET State = dbo.MyFunction(Zip)

--Angel
-----------------------------------
SELECT * FROM users WHERE clue > 0
0 row(s) affected

 

[mwolf00]

The best way to do that is to insert the rows into a temp table with and IDENTITY column.

SELECT @processRow = 1
SELECT @ttlRow = count * FROM yourTempTable
WHILE @processRow <= @ttlRow
BEGIN
SELECT * FROM yourTempTable WHERE idCol = @processRow
--process data
--insert result into new temp table
DELETE FROM yourTempTable WHERE idCol = @processRow
SELECT @processRow = @processRow + 1
END ---------------------------------------
HAPPY THANKSGIVING!!!!
mikewolf@tst-us.com

 

[tlbroadbent]

mwolf00's code can replace the use of a cursor but it is still procedural code and will not likely result in any performance improvement over using a cursor. The best thing you can do is stop thinking of stepping through a table one record at a time and focus on relational principles of sets of data.

SQL is optimized for processing sets of data not running procedural code and looping through row sets. Sometimes, requirements necessitate procedural code but most of the time proper SQL programming can avoid such costly processes. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.