Hello, so I have written several stored procedures that act on individual rows of data by taking in an ID number. I would like to keep several stored procedures that can call this stored procedure at different levels of my database scheme. For instance, when a row is inserted I call this stored procedure. When something else is modified I would like to call this stored procedure for each line. This is so I can have one set of base code that can be called everywhere else but that acts on different amounts of data. I have been able to produce this result with Cursors, but I am told these are very inefficient. Is there any other way to produce this kind of functionality without sacrificing performance? Thanks.
+1
A:
Yes. Use standard joins to operate on sets rather than RBAR (Row By Agonising Row). i.e. Rather than call a function for each row, design a join that performs the required operation on every applicable row as a set operation.
I often see devs use the 'function operates on a each row', and although this seems to be the obvious way to encapsulate logic, it doesn't perform well on SQL Server or most DB engines.
In some circumstances, a table-valued function can be used effectively (MS SQL Server).
(BTW, you are correct in saying cursors are inefficient).
Mitch Wheat
2010-08-16 00:30:24
Hmm, I thought you were not able to call stored procedures in a select statement. How can I use a stored procedure that takes in an ID number and call it within joins? Thanks.
Bob L
2010-08-16 00:33:18
@Bob L: I'm suggesting you don't.
Mitch Wheat
2010-08-16 00:39:11
I see... So make it a function or something?
Bob L
2010-08-16 00:39:42
I don't know if this is what you are saying but would something like this be correct? update table set column1=GetFK(column1)
Bob L
2010-08-16 00:46:23
@Bob L: Yes, that won't perform well.
Mitch Wheat
2010-08-16 02:33:56