Stored Procedures - Good or Bad?

[SQL_n00b]

I have been reading up quite a bit on the topic of Stored Procedures (SPs) and keep coming across contradicting opinions from various 'SQL experts' on whether or not they should be used. Sometimes it's a downright 'No' (Frans Bouma's Blog for example -- google the term for the article), while at other times it is a strong 'Yes' (Rob Howard's blog). What reading those and a few more articles did was create more confusion on the topic. So I thought of putting up a question here to see what people around here think about SP.

The reason I began exploring SPs is because on my website, I need to run an update every few days/weeks that involves a series of complex calculations based on the data in the DB at the time. I believe the best way to do is to run a proc myself, rather than trust the user to remember to do it on the front-end (or through the use of a server-side script).

Any ideas/opinions?

[sknake]

Stored procedures have their advantages and disadvantages like every other technology. A lot of people recommend SPs because they cache the execution plan, which they do, but if you form a query properly using parameterized SQL and don't build the query dynamically then it also uses a cached execution plan. When you deploy sprocs you also need to grant permissions on it where you probably would not have to with a query so it can be a pain at times.

Another key advantage is the size of the query. If you have a 10k line query used to do a massive upset then it is probably better to use a sproc with parameters rather than transmitting that query across the wire every time you execute it.

What you're describing sounds like a maintenance task that is best suited as a stored procedure. You will need to set up an SQL Task or application to execute the sproc on an interval and if you need to change the sproc (unless parameters change) then you can use ALTER PROCEDURE instead of recompiling the executing assembly/application that would run it otherwise. It also gives you consistency if the maintenance task may be executed from more than one machine and you may have multiple versions of software out there -- they will all run the same sproc.

From what you have said I vote for a sproc, however I tend to stay away from them as much as I can :)

[SQL_n00b]

What you're describing sounds like a maintenance task that is best suited as a stored procedure. You will need to set up an SQL Task or application to execute the sproc on an interval and if you need to change the sproc (unless parameters change) then you can use ALTER PROCEDURE instead of recompiling the executing assembly/application that would run it otherwise. It also gives you consistency if the maintenance task may be executed from more than one machine and you may have multiple versions of software out there -- they will all run the same sproc.

I decided to create a trigger on UPDATE, so that every time a batch of values is entered in the DB, the trigger is fired. The algorithm for my trigger is as follows:

1. Check if DesiredColumn IS NULL.
2. If it is, loop through each row in the table and compare Column A with Column B.
If DesiredColumn IS NOT NULL, do nothing.
3. If Column A = Column B, perform calculations on Column B and set the result in DesiredColumn

I cannot seem to get my head around how to loop through each record. The first error I encountered was

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

So I tried using the 'IN' keyword, but that does not seem to be correct either. How do I loop through the records in the DB?

Thanks!

[sknake]

You don't want to loop through records in a database. You can but it kind of defeats the purpose of databasing. Typically there are better ways of going about things. Please post what code you have so far so I can get a better idea of what you're trying to accomplish and we'll go from there.

There are limitations with triggers by the way. I created a trigger once to update calculations but as the database grew it began to fail. The reason was there is a hard coded limit of N records that a trigger can update. IE if a trigger updates more than 1000 records it will fail. I forget the exact number but there is a ceiling.

[SQL_n00b]

You don't want to loop through records in a database. You can but it kind of defeats the purpose of databasing. Typically there are better ways of going about things. Please post what code you have so far so I can get a better idea of what you're trying to accomplish and we'll go from there.

There are limitations with triggers by the way. I created a trigger once to update calculations but as the database grew it began to fail. The reason was there is a hard coded limit of N records that a trigger can update. IE if a trigger updates more than 1000 records it will fail. I forget the exact number but there is a ceiling.

Thanks for your replies sknake. I am not at work right now, so do not have access to the code- will post the code when I get back to work tomorrow.
In the meanwhile, could you please clarify the following?
I agree that as the DB grows, looping is not the most efficient way to do what I am trying to. It will slow down the whole process - not to mention that (as you said), the trigger might fail altogether. However, since the value in DesiredColumn is based purely on what's entered in Columns A & B, what is the alternative for looping. From whatever research I have done on this subject, StoredProcs/Triggers seem to be the only way to do this. I know I am missing something, but with my limited SQL knowledge can't quite figure it out!

[sknake]

Can't you just do something like this:

Select Sum(ColumnA + ColumnB)
From Table
Where Condition = Value

or

Declare @someVal money
Set @someVal =
(
  Select Sum(ColumnA + ColumnB)
  From Table
  Where Condition = Value
)

--Assign the @someVal to a field in your trigger for the update. Or run an update on the table.

[SQL_n00b]

This was my first time I was using a trigger and apparently I was not using the correct syntax (was missing the USE myDBname; GO statements) in creating the trigger which was causing it to not run as I was expecting it to.

Anyway, all seems fine now. Thanks for all your help sknake!