Advantages of Stored Procedures?

Hi,
Generally I write all my SQL in Stored Procedures instead of using adhoc queries. But I dont feel good about stored procedures when I come across situations like this.

Lets say that I have a stored procedure something like this

CREATE PROCEDURE dbo.proc_MYSP
@.CaseID char(10)
AS
SELECT * FROM TABLE1WHERE CASEID = @.CASEID

Suppose in future if the field CASEID is changed to char(20) then I need to change the declaration of CaseID in all my stored procedures that take CaseID as input parameter. If I write adhoc queries then I need not worry about this. Is there any effective solution for a situation like this.

Thank you.>>If I write adhoc queries then I need not worry about this...

you will have that prob no matter if you use stored procs or adhoc queries. you'd still need to make the changes whereever req'd. its just less pain to make the changes in the stored procs than asp pages. business logic is best kept at the backend. its like a rule.

hth|||mndinkar, I seriously hope you meant this as a joke. See:

::you will have that prob no matter if you use stored procs or adhoc queries. you'd still need
::to make the changes whereever req'd. its just less pain to make the changes in the stored
::procs than asp pages. business logic is best kept at the backend. its like a rule.

There is a couple of things that need to be ccorrected here.

First, you seriously oversimplify. See, we do pretty complex apps. I use dynamic SQL. Still I haeve no sql at all in any ASP page. Basically: dude, get real. THere is a lot you can make between writing SP's and putting the SQL into asp pages. Stuff like working with classes. Or, if you want to be primitive, stuff like centralising all your SQL statements in one class.

Second, basically, a SP like the one shown above - nothing more than a select - givesyou nothing but adds. It is another layer that has to be maintained, and it is one that is badly integrated into the development environment (integration with VSS, for example, or the requirements for debugging). How can maintaining more than you have be less painfull? When you gain nothing and only loose?

Third, the statement that business logic per se is best kept at the backend is bordering on irresponsibility. The complete school of modern software architecture goes against putting business logic into the data store. Multi tiered applications are the modern concept of how to make software, and no, they are not about dumping all your business logic into the database. Quite the contrary.

Don't get met wrong, there is nothing wqrong with using SP's iin certain situations. Just when you advice to use them - no. They just cost you.|||Take a look at this, it's a very good cool headed approach to the issues.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndotnet/html/storedprocsnetdev2.asp|||And for a good hot-headed discussion on the issue, see Rob Howard's classic blog post and repliesDon't use stored procedures yet? Must be suffering from NIHS (Not Invented Here Syndrome) And of course Frans Bouma's rebuttal blog postStored procedures are bad, m'kay?.

I at least thought it was interesting reading.

Terri|||those were interesting links terri. got to know a few things. thanks.