SQL Server - standard pattern for doing row by row operations on a table/view

views:

1112

answers:

+2 Q:

SQL Server - standard pattern for doing row by row operations on a table/view

I want to iterate through a table/view and then kick off some process (e.g. run a job, send an email) based on some criteria.

My arbitrary constraint here is that I want to do this inside the database itself, using T-SQL on a stored proc, trigger, etc.

Does this scenario require cursors, or is there some other native T-SQL row-based operation I can take advantage of?

+1 A:

Your best bet is a cursor. SQL being declarative and set based, any 'workaround you may find that tries to force SQL to do imperative row oriented operations is unreliable and may break. Eg. the optimizer may cut out your 'operation' from the execution, or do it in strange order or for an unexpected number of times.

The general bad name cursors get is when they are deployed instead of set based operations (like do a computation and update, or return a report) because the developer did not found a set oriented way of doing the same functionality. But for non-SQL operations (ie. launch a process) they are appropriate.

You may also use some variations on the cursor theme, like client side iterating through a result set. That is very similar in spirit to a cursor, although not using explicit cursors.

Remus Rusanu 2009-07-05 01:20:57

+2 A:

The standard way to do this would be SSIS. Just use an Execute SQL task to get the rows, and a For Each task container to iterate once per row. Inside the container, run whatever tasks you like, and they'll have access to the columns of each row.

John Saunders 2009-07-05 01:21:13

@John Using ssis, how would I ensure that each row only gets operated on once (assuming I wanted the ability at some point to run multiple concurrent ssis tasks against the jobs table in order to achieve greater throughtput)? I suppose I'd need some temporary "in progress" flag to avoid contention. Is this something ssis can handle for me or would I still need to implement this logic myself?

Howiecamp 2010-03-13 20:11:50

@Howiecamp: I don't know what you mean. The Execute SQL task would produce a resultset containing rows. The For Each task would operate once per row. What else could you need to do to ensure each row only gets operated on once?

John Saunders 2010-03-14 01:15:54

If you are planning on sending an email to each record with an email address (or a similar row-based operation) then you would indeed plan on using a cursor.

There is no other "row-based" operation that you'd do within SQL itself (although I like John's suggestion to investigate SSIS - as long as you have SQL Server Standard or Enterprise). However, if you are summing, searching or any other kind of operation and then kicking off an event once done the entire selection set, then you would certainly not use a cursor. Just so you know - cursors are generally considered a "last resort" approach to problems in SQL Server.

Mark Brittingham 2009-07-05 01:21:46

<< cursors are generally considered a "last resort" approach to problems in SQL Server. >>Why?

Howiecamp 2009-07-05 02:01:12

You generally will incure performance hit when using cursor, thus it's best to avoid them when you can.

Jimmy Chandra 2009-07-05 02:25:47

Keep in mind though that it is perfectly fine to use a Cursor for the right kind of operation. The problem is that people who are new to SQL often use cursors to do things that really are better done using set operations (moving a subset of records from one table to another based on some criteria, for example). Thus, experienced SQL developers are a bit hesitant to recommend them to newcomers for fear that we'll get you started down the wrong path. I'd by lying if I said that *I* never used them, though ;-)

Mark Brittingham 2009-07-05 03:20:19

The first thought which comes to my mind when I need to iterate over the result set of a query is to use cursors. Yes, it is a quick and dirty way of programming. But cursors have their setbacks as well - They incur overheads and can be performance bottle necks.

There are alternatives to using cursors. You can try using a temp table with an identity column. Copy you table to the temp table and using a while loop to iterate over the rows. Then based on a condition call your stored procedure.

Here, check this link for alternatives to cursors - http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1339242,00.html

cheers

Andriyev 2009-07-05 01:24:09

I have to admit that I don't have a clue what you are saying. If you copy results to a temp table, you still have to iterate. A "While" loop can be useful while *using a cursor to iterate* or for continually testing a condition, but how does it help you select a specific row's data and do something (e.g. sending an email)?

Mark Brittingham 2009-07-05 01:32:05

Yes, it is true that iteration would be done in order to loop over a temporary table. In fact even SQL Server itself implements cursors by creating an in-memory temporary table. Then there are caveats like failure to close a cursor which would result in resources being occupied till the SQL connection is closed.Specific's row's data can be tested using a select query on the temporary table with a 'where' clause on the identity column and the while loop would check identity column's value against the rowcount. I hope this helps.

Andriyev 2009-07-05 02:27:48

ansaurus

tags:

views:

answers:

SQL Server - standard pattern for doing row by row operations on a table/view

related questions